Excel's calculations are off a few cents!

[Natalie Rowe]

I can't figure out why my TOTAL for $ column is off from
1-5 cents. When I check the amounts going across, they
are correct, but the amount at the bottom of the column
is off!! What am I doing wrong?
Any help would be greatly appreciated, as I have to
manually check all Excel's figures.

 

[Dan E]

View more decimal places Natalie, does it still add up incorrectly??
I'm guessing your summing calculated values, that are viewed at less precision than calculated.

You can use ROUND(formula, #decimals) to make the calculations round to the precision you desire, or you can set "Tools" ->
"Options" and under the calculation tab check off "Precision as displayed"

Dan E

 

[Chip Pearson]

Natalie,

This is likely due to the fact that you have calculated cells
(cells with formulas) that return values with several decimal
places, but you are displaying only two decimal places. Excel
calculates with the underlying value of the cell, not the
displayed value of the cell.

For example, suppose you have three cells each with the value
0.123 but are displayed as 0.12. The sum of these cells is 0.369,
which would be displayed as 0.37, rather than the "visually
accurate" value 0.36.

You can force Excel to calculate with displayed values by going to
the Tools menu, choose Options, then the Calculate tab. There,
check the "Precision as displayed" option.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com (e-mail address removed)

 

[Bernard V Liengme]

Hi Natalie,
Sound like a formatting thing. Suppose I have 4.985 and 4.986 in two cells
(a1 and A2) and the cells are formatted to display two decimals (cells show
4.99 and 4.99) If B1 has =A1+A2 then it computes 4.985+4.986 = 9.971 and if
this cell is formatted to show two decimals we see 9.97 when we expect 9.98.
If SUM is used with more than two numbers we can get up to a 5 cent error.
This is common with all computerized apps not just Excel.

The solution is to (a) use the ROUND function rather than just formatting or
to set Excel (Tools?options...) to use the precision as displayed. The
latter can get you into trouble if Excel is used for more advance
calculations.

Want more info on ROUND? Send me your worksheet and I will fix it (we are
having three days of rain .....and I'm bored)

Bernard