Why does rounding create inaccurate sums?

[snocaps]

I have some rows with calculations, which I include in a round function (=round(calculation,2). The rounding calculation works fine, but when I sum two cells that use the round function, the sum doesn't always calculate correctly. It's not obvious either, until I export the file for import to another program, and I end up with unbalanced data. Here's an example -- the long decimal doesn't show until about 15 positions out, when it changes from 2 positions followed by zeroes to what you see here:


(218.330000000000000000000000000000) calculated w/ round

226.810000000000000000000000000000 calculated w/ round

8.479999999999990000000000000000 sum of 2 numbers above


Short of putting the sum in a rounded function (which really isn't practical, because it's a subtotal, not a true sum), what else can be done to fix this?

 

[Hui]

If your 2 values are in A1 & A2

you can do something like:

=INT(100*(A1+A2))/100

or

=ROUND(A1+A2,2)

 

[NARAYANK991]

Hi Jenny ,


The issue you have posted has nothing to do with rounding ; in any unused cells , enter the two values -218.33 and 226.81 ; in a third cell , say M10 , put in the formula to add these two cells together ; if your default format is General , you will see the value 8.48.


Now , in a separate cell , put in the formula =8.48=M10 ; you will see FALSE displayed.


This is because when Excel is calculating the sum / difference of two values which are big , or two values which are very small , the calculation is never exact.


As an example , take the values 218.33 ( positive , this time ) and 226.81 ; you would expect that the sum of these two values will give you 445.14 ; sum these two values and then use the formula =445.14=M10 , where M10 contains the sum of the two values ; you will again get FALSE.


The lesson is that if you want exact values , you have to use the ROUND function ; Excel on its own may or may not give you the result you expect.


If you are interested in knowing more , refer this :


http://support.microsoft.com/kb/78113


Narayan

 

[snocaps]

Fascinating! And here I thought that 1+1=2. Evidently that's not exactly precise.


I think I'll set my "precision as displayed" as indicated in the article. Hopefully that will "fix" it so I won't forget & create the error again.


Thank you so much.