Results 1 to 4 of 4

20090305, 00:59 #1
 Join Date
 Jan 2001
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 339
 Thanks
 0
 Thanked 0 Times in 0 Posts
0.5  0.4  0.1 equals 0 right?
If you put that in Excel as purely "=0.50.40.1" it gets this correct.
Can you then explain to me WHY "=(0.50.40.1)" returns the answer "2.77556E17"
If I change the cell formatting from general to number it shows correctly at 0, but why the error in the first place?

20090305, 01:24 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 28 Times in 28 Posts
It must be some kind of rounding error. Excel stores numbers in binary format (existing exclusively of 0s and 1s). Most decimal numbers cannot be represented exactly in binary format, so the stored value will differ very slightly from the 'real' value. You don't see this because the rounding error is smaller than the smallest value that Excel can display. In calculations, however, these differences may accumulate so that they become visible in the end result.
But I can't explain this particular example  apparently the parentheses change the way Excel evaluates the calculation. You can get "round" it by using the ROUND function:
=ROUND(0.50.40.1,2)
this will round the stores result to 2 decimal places, whereas the number format only changes the displayed value.

20090305, 01:36 #3
 Join Date
 Jan 2001
 Location
 Canberra, Australian Capital Territory, Australia
 Posts
 339
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='HansV' post='763537' date='05Mar2009 18:24']It must be some kind of rounding error. Excel stores numbers in binary format (existing exclusively of 0s and 1s). Most decimal numbers cannot be represented exactly in binary format, so the stored value will differ very slightly from the 'real' value. You don't see this because the rounding error is smaller than the smallest value that Excel can display. In calculations, however, these differences may accumulate so that they become visible in the end result.
But I can't explain this particular example  apparently the parentheses change the way Excel evaluates the calculation. You can get "round" it by using the ROUND function:
=ROUND(0.50.40.1,2)
this will round the stores result to 2 decimal places, whereas the number format only changes the displayed value.[/quote]
Thanks Hans .... really annoying but it does explain it. Thanks for your efforts

20090305, 07:39 #4
 Join Date
 Feb 2001
 Location
 Yilgarn region of Toronto, Ontario
 Posts
 5,453
 Thanks
 0
 Thanked 0 Times in 0 Posts
[quote name='HansV' post='763537' date='05Mar2009 03:24']apparently the parentheses change the way Excel evaluates the calculation.[/quote]
My guess is that the code that pushes an expression on the stack and evaluates an intermediate result, forced by using the parentheses is indeed different from the code that calculates a result.
A good clue always is to apply the 1960s rule "Two to the ten equals ten to the three".
I see the E17 and treat it is as E18.
E18 represents 18 (base ten) zeroes.
18 base ten zeroes is 6 lots of three base ten zeroes
"Two to the ten equals ten to the three", implies 60 lots of base two digits, and hence a 60 bit word.
Although why all this is running on a CDC CYBER I have no idea.
Perhaps a more scrutinous calculation would push my backoftheenvelope calculation to 63 or 64 bits, which would make complete sense.