Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
rounding absolute zero
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
rounding absolute zero
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/default...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 -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "amaries" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
rounding absolute zero
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
rounding absolute zero
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 -- Bernard V Liengme Microsoft Excel MVP www.stfx.ca/people/bliengme remove caps from email "amaries" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I need a formula with rounding up & rounding down to the nearest . | Excel Worksheet Functions | |||
Absolute path | Excel Discussion (Misc queries) | |||
Absolute Value | Excel Worksheet Functions | |||
absolute value | Excel Worksheet Functions | |||
Absolute average | Excel Worksheet Functions |