rounding absolute zero

[amaries]

I just ran into an unusual issue with summing three numbers.
Cells formated as accounting, no symbol, 2 decimal. On all other sums I
have the zero values displaying as - instead of 0.00. But in this strange
case these three numbers are displaying as 0.00. When I change the decimal
places to 25 you see 'extra' numbers appear in the sum. There are no other
formulas or formating tied to these cells.
These are the values I am summing:
(606.02)
643.20
(37.18)
Sum is showing 0.00

If I change the column to 25 decimals you see:
(606.0200000000000000000000000)
643.2000000000000000000000000
(37.1800000000000000000000000
Sum is showing as 0.0000000000000639488462184
Where are these numbers coming from? No, I'm not a 'math' guru, I'm
actually helping the company controller solve the mystery.

I know its not the formating since currency or number have no effect and
putting other values in the same cells don't have the issue. Only this
particular combination of numbers being summed.

Can anyone clue a non-math specialist in? And in such a way I can translate
to our controller?

Thanks

 

[Bernard Liengme]

There is no real mystery once you are "in" on the secret. It all has to do
with the fact that computers store numbers in binary (base 2) while
10-fingered humans use base 10 (decimal numbers).
Most computer apps (not just Excel) use the IEEE convention which limits the
precision of the stored values. Just as there is no way to exactly represent
1/3 (one-third) as a decimal (0.33333333...........for ever) so some real
decimal (numbers that are not integers) cannot be exactly represented in
binary with the finite number of paces required by the IEEE convention.

You can always avoid the problem by using formulas sis as
=ROUND(SUM(a1:A3),12) since Excel will be accurate to 12 places.

Want to know more? Read one or more of these:
http://support.microsoft.com/defaul...pport/kb/articles/Q42/9/80.ASP&NoWebContent=1
http://support.microsoft.com/kb/78113/en-us
What Every Computer Scientist Should Know About Floating Point
http://docs.sun.com/source/806-3568/ncg_goldberg.html
http://www.cpearson.com/excel/rounding.htm
best wishes

 

[amaries]

Thank you, thank you!
I left the controller scratching his head but I think it's nailed. It is
scary how users sometimes assume things without really taking the time to
understand the application. Especially when critical business decisions are
made off the data.

Best regards

 

[Bernard Liengme]

Thanks for the feedback. If comptroller gives you more headaches tell him to
do it in COBOL which does not use IEEE and never has (had?) this problem