Scientific number format giving wrong result

Topic

New Reply

0.5 – 0.4 – 0.1 equals 0 right?

If you put that in Excel as purely “=0.5-0.4-0.1” it gets this correct.

Can you then explain to me WHY “=(0.5-0.4-0.1)” returns the answer “-2.77556E-17”

If I change the cell formatting from general to number it shows correctly at 0, but why the error in the first place?

Reply | Quote

Replies

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.5-0.4-0.1,2)

this will round the stores result to 2 decimal places, whereas the number format only changes the displayed value.

Reply | Quote

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.5-0.4-0.1,2)

this will round the stores result to 2 decimal places, whereas the number format only changes the displayed value.

Thanks Hans …. really annoying but it does explain it. Thanks for your efforts

Reply | Quote

apparently the parentheses change the way Excel evaluates the calculation.

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 E-17 and treat it is as E-18.
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 back-of-the-envelope calculation to 63 or 64 bits, which would make complete sense.

Reply | Quote