CODEWITHSUNDEEP
excelfloating-point
Simon Byrne
Simon Byrne

Reputation: 7874

Specification of Excel floating point behaviour

Though Excel uses the standard IEEE754 binary64 format, it doesn't use the same rules for arithmetic and comparisons, e.g. 

=0.1+0.2=0.3

return TRUE, and other examples.

Is the exact behaviour documented anywhere?

Upvotes: 2

Views: 763

Answers (2)

Simon Byrne
Simon Byrne

Reputation: 7874

Having done some experiments, I have established the following behaviour:

  1. Two values are "equal" according to the equality operator (=) if they are equal to 15 significant decimal digits (i.e. "%.14e" in printf lingo)

    • Less than (<) and less than or equal (<=) are defined to be consistent with this (i.e. "Excel less than" is true if it is true under IEEE arithmetic, and the values are not "Excel equal").

  2. if the final operator (according to usual operator precedence) in a cell formula is - or +, and the magnitude of the result is less than 8 ulps (units in last place) of the first argument, then the result is set to zero.

    • this behaviour can be suppressed by wrapping the whole expression in parentheses (or equivalently, you can view such wrapping parentheses as an operator).

  3. SUM appears to act in the same way as a chained sequence of + (the threshold appears to be 8 ulps of the second last element of the cumulative sum), but the truncation cannot be suppressed via wrapping parentheses.

Note that these are not consistent, in that values may be "Excel equal" but have a non-zero difference, and vice-versa.

Therefore, if you want something closer to IEEE arithmetic in Excel:

  • wrap all formulas in a final parentheses

  • use (a-b)<0 instead of a<b (and similarly for other boolean operators)

  • avoid use of SUM

(this will still not be strict due to lack of subnormals and signed zeros)

Upvotes: 3

John Coleman
John Coleman

Reputation: 52008

This documentation tangentially addresses this:

https://support.microsoft.com/en-us/kb/78113

See especially:

Excel 97, however, introduced an optimization that attempts to correct for this problem. Should an addition or subtraction operation result in a value at or very close to zero, Excel 97 and later will compensate for any error introduced as a result of converting an operand to and from binary.

If they apply this "optimization" to the difference between 0.1 + 0.2 and 0.3 it apparently is converted to 0, which does mean that they are not following the standards strictly.

Upvotes: 1

Related Questions