Excel does not sum properly

Question

On my worksheet, I have a column formatted as currency. I have two decimal points. In this column, I have smaller columns separated by blank rows. When I try to sum each smaller column, I don't get the correct amounts. For example:

$190.92

$118.66

$190.92

$118.66

$190.92

$118.66

$190.92

$118.66

The total is $1,238.30. It should be $1,238.32.

All of the cells are formatted for currency with two decimal places. I changed it to four decimal places just to see and it came up with $1,238.3040.

I typed the formula instead of using the autosum and I have the same issue.

I get a new worksheet every month and I have to go through and add all of the separate columns and compare them to what we are being charged. Yes, we're only talking a few cents but there are so many of these section and each of these are 1-2 cents off and it adds up, especially when I do this every month.

Any ideas? Thanks.

Answer 1

Since your values are not exactly equal to the displayed values, their sum is not equal to the sum of the displayed values.

Take a very simple example: you have values 0.123 and 0.234, displayed as $0.12 and $0.23. Their sum is 0.357, displayed as $0.36. But the sum of the displayed values is $0.35.

If you want the sum to be the sum of the displayed values, change a formula such as =SUM(B2:B9) to =SUM(ROUND(B2:B9, 2))

If you do not have Microsoft 365, Office 2024 or Office 2021, confirm the formula by pressing Ctrl+Shift+Enter.