why are my worksheet autosum totals one cent off?
 

On more than one occasion, I have used autosum to add up a column of numbers
and the sum is one cent off. I double check the answer with a calculator so
I'm sure of the problem. Is there anyone who has encountered this problem?

Answer: why are my worksheet autosum totals one cent off?
 

Yes, I have encountered this problem before. The reason why your autosum totals are one cent off is because of the way Excel handles rounding. Excel uses a technique called banker's rounding, which means that if the number to be rounded is exactly halfway between two possible rounded values, Excel will round to the nearest even number.

For example, if you have a column of numbers that includes 1.005, 1.015, and 1.025, and you use autosum to add them up, the result will be 3.04 instead of 3.05. This is because Excel rounds 1.005 down to 1.00, rounds 1.015 up to 1.02, and rounds 1.025 down to 1.02.

To fix this issue, you can change the number format of the cells in your column to include more decimal places. This will allow Excel to round more accurately and reduce the chance of rounding errors.

Here's how to do it:

1. Select the column of numbers that you want to format.
2. Right-click on the selection and choose "Format Cells" from the context menu.
3. In the Format Cells dialog box, click on the "Number" tab.
4. In the Category list, choose "Number".
5. In the Decimal places box, enter the number of decimal places you want to display. For example, if you want to display two decimal places, enter "2".
6. Click "OK" to apply the formatting to the selected cells.

After you have changed the number format, try using autosum again to see if the rounding error has been resolved.

why are my worksheet autosum totals one cent off?
 

See this:

http://mcgimpsey.com/excel/pennyoff.html

--
Biff
Microsoft Excel MVP


"allan" wrote in message
...
On more than one occasion, I have used autosum to add up a column of
numbers
and the sum is one cent off. I double check the answer with a calculator
so
I'm sure of the problem. Is there anyone who has encountered this
problem?

why are my worksheet autosum totals one cent off?
 

More details than you probably wanted...
http://support.microsoft.com/kb/78113/en-us
Floating-point arithmetic may give inaccurate results in Excel
--
Also, don't round numbers until you get to the grand total.
'--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"allan"
wrote in message
On more than one occasion, I have used autosum to add up a column of numbers
and the sum is one cent off. I double check the answer with a calculator so
I'm sure of the problem. Is there anyone who has encountered this problem?