I understand perfectly why 0.1 + 0.2 is not equal to 0.3 due to the floating point. In most of programming languages, 0.1 + 0.2 == 0.3 is False.
But in Excel if(0.1 + 0.2 == 0.3; 1; 0) gives 1
Asked
Active
Viewed 429 times
4
-
Possibly useful: https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel – BigBen Oct 18 '19 at 13:53
-
@BigBen: I see that Excel also uses the IEEE 754 floating point as other languages, which indeed causes the comparison to be false – Metariat Oct 18 '19 at 13:55
-
Related: https://stackoverflow.com/q/40293983/270986 – Mark Dickinson Oct 18 '19 at 15:22
1 Answers
8
The reason this happens in Excel is because Excel only keeps track of 15 digits of precision. Floating point math for 0.2 + 0.1 results in 0.30000000000000004, and that 4 way out there is the 17th digit. That means Excel just truncates everything after the 15th 0 and is left with 0.300000000000000 which = 0.3
See here for more info: https://en.wikipedia.org/wiki/Numeric_precision_in_Microsoft_Excel
tigeravatar
- 26,199
- 5
- 30
- 38
-
1I think it's a bit more complicated than this in practice; there's some hidden magic going on. See for example https://stackoverflow.com/q/53337274, https://stackoverflow.com/q/40293983, https://stackoverflow.com/q/36452194 – Mark Dickinson Oct 18 '19 at 15:25
-
You're saying that when Excel compare `0.30000000000000004` to `0.3`, it's actually comparing two strings? Because I don't know how it keeps track of only 15 digits. – Metariat Oct 30 '19 at 13:04