Excel Not Adding Correctly

Question

I have a spreadsheet and cannot get the cells I select to add correctly. Each has the formula of =1293.00/1000*C4 (for example, and C4 is a 2.345 decimal number if that makes a difference). I have it set to currency and no numbers after the decimal, so it rounds it and then at the bottom I have a cell set to total everything. I am using this formula =sum(D4,D7,D10,D14) and it comes to $3,429.00 and when I manually add it on my calculator it comes to $3,431.00, I have added it up multiple times to make sure it isn't just operator error. I have also checked each cell; none is set to text and automatic adding is turned on. At first, I thought maybe it was because I copied and pasted but I opened a brand-new spreadsheet and typed everything in again with still no luck. I have no hidden columns and no hidden rows. I also have come across the problem where I am a penny off in a cell that I am trying the total. It is a penny off in either direction, I either need to add a penny or subtract a penny to get it to come out correct. Makes it really hard to do a pay application. I don't get any error messages with either problem. Any help is appreciated.

Answer 1

Set the cells to display - say - 4 decimal places. You'll probably see where the discrepancy comes from.

You might round each intermediate result to 0 decimal places.

Answer 2

Hi Charity,

Change the format of the cells that you are using in your SUM formula to Number and two decimal places. This will show the exact values that Excel is using in the calculation.

Use the ROUND function in your formulas to round the values to the nearest whole number before adding them. For example, instead of =1293.00/1000C4, use =ROUND(1293.00/1000C4,0). This will ensure that the values are consistent with the currency format and the manual calculation.

Regards, Sola