Forum Discussion
Formula gives wrong result
In Excel365 i want to know, if a given cell content is divisable by 10.
For cell R9, which has a value of 11440, i use the formula =REST(R9;10) . The ouput is 0, as expected.
For cell R10, which has a value of 12870, i use the formula =REST(R10;10) . The ouput is 10, which is wrong. I cannot understand this output. What could be the reason? Thanks for help.
HansVogelaar wrote: ``Does it look like [...] 12869,999999999999999``
Well, more likely 12869,999999999900000. (15 significant digits; wink)
Also format the MOD/REST formula to display 15 decimal places. Does it look something like 9,99999999999999, not actually 10?
The work-around is to round calculated values to the expected precision, based on the precision of the data. For example:
=REST(ROUND(R10; 0); 10)
Alternatively, round the calculation in R10.
-----
The reason is: binary arithmetic anomalies.
The root cause is: most decimal fractions cannot be represented exactly in 64-bit binary floating-point, which Excel (and most applications) uses to represent numbers internally. Consequently, numbers with decimal fractions are approximated.
Moreover, the binary approximation of a particular decimal fraction might vary depending on the magnitude of the number, because some binary digits ("bits") must be used to represent the integer part. That might result in a loss of precision.
That is why, for example, 10.01 - 10 = 0.01 returns FALSE(!).
5 Replies
Format R10 as a number with 15 decimal places. Does it look like 12870,000000000000000 or like 12869,999999999999999?
HansVogelaar wrote: ``Does it look like [...] 12869,999999999999999``
Well, more likely 12869,999999999900000. (15 significant digits; wink)
Also format the MOD/REST formula to display 15 decimal places. Does it look something like 9,99999999999999, not actually 10?
The work-around is to round calculated values to the expected precision, based on the precision of the data. For example:
=REST(ROUND(R10; 0); 10)
Alternatively, round the calculation in R10.
-----
The reason is: binary arithmetic anomalies.
The root cause is: most decimal fractions cannot be represented exactly in 64-bit binary floating-point, which Excel (and most applications) uses to represent numbers internally. Consequently, numbers with decimal fractions are approximated.
Moreover, the binary approximation of a particular decimal fraction might vary depending on the magnitude of the number, because some binary digits ("bits") must be used to represent the integer part. That might result in a loss of precision.
That is why, for example, 10.01 - 10 = 0.01 returns FALSE(!).
- @dcheikey HansVogelaar JoeUser2004
My problem is solved: I get the correct result if y use =REST(ABRUNDEN(R10;0);10)
Thanks everybody for help! I have read several time about this problem of precision, but i never thougt, that it would never ever concern me as a greenhorn 🙂
- See my answer below! unfortunately, i missed teh correct spelling of your email-adress 😞
