6

The following expression evaluates to false in C#:

(1 + 1 + 0.85) / 3 <= 0.95

And I suppose it does so in most other programming languages which implement IEEE 754, since (1 + 1 + 0.85) / 3 evaluates to 0.95000000000000007, which is greater than 0.95.

However, even though Excel should implement most of IEEE 754 too, the following evaluates to TRUE in Excel 2013:

= ((1 + 1 + 0.85) / 3 <= 0.95)

Is there any specific reason for that? The article linked above does not mention any custom implementations of Excel that can lead to this behavior. Can you tell Excel to strictly round according to IEEE 754?

Please note that even though most Excel questions should be asked on superuser.com, this question deals with floating-point arithmetic, which is a common problem in programming languages. From the viewpoint of this question's topic, Excel is a programming language like C# or Java.

Jonas Sourlier
  • 13,684
  • 16
  • 77
  • 148
  • Out of interest, what result does Excel report for `= ((1 + 1 + 0.85) / 3 - 0.95)`? – Mark Dickinson Apr 06 '16 at 13:18
  • @MarkDickinson `1.11022E-16`. – Jonas Sourlier Apr 06 '16 at 13:26
  • Interesting. Sounds like the arithmetic operations are following IEEE 754 semantics, but the `<=` is doing some kind of fuzzy check. – Mark Dickinson Apr 06 '16 at 13:29
  • 3
    This article has a section on Excel: https://www.cs.berkeley.edu/~wkahan/Mindless.pdf Key quote: “How can a user of Excel predict which functions act upon displayed instead of actual values? Which expressions get rounded cosmetically before being displayed? The user’s program cannot be debugged without an awareness of these questions, and an aware user ends up debugging Microsoft’s pious fraud instead of just a malfunctioning Excel spreadsheet.” – Pascal Cuoq Apr 06 '16 at 13:42
  • 1
    In Excel `=(1 + 1 + 0.85) / 3` will be `0.95000000000000` as it "... stores only 15 significant digits in a number, and changes digits after the fifteenth place to zeroes". See https://support.microsoft.com/en-us/kb/269370. – Axel Richter Apr 06 '16 at 13:49
  • 2
    @AxelRichter: That doesn't seem to be the case in practice, though: if I enter `=(1 + 1 + 0.85) / 3` into cell A1, and `0.95` into cell A2, then `=(A1 - A2)` still gives me a value displayed as `1.11022E-16`. If the value stored were actually (the closest representable double to) 0.95, then `=(A1 - A2)` would give zero. (Though `=(A1 = A2)` does give me TRUE.) Kahan's quote is spot on here. – Mark Dickinson Apr 06 '16 at 13:55
  • @Mark Dickinson: `=A1-A2` without the parentheses around will give zero ;-). So you can't trust Excel's number precision over 15 significant digits. – Axel Richter Apr 06 '16 at 14:02
  • @AxelRichter: Aargh! So it does. That's truly horrible. Is *any* of this documented anywhere? – Mark Dickinson Apr 06 '16 at 14:42
  • @Mark Dickinson: As far as I know, it's not documented. There are much more different behaviors with floating point precision in Excel. Pivot table vs. worksheet, http://stackoverflow.com/questions/36383419/microsoft-excel-pivot-miscalculation-in-sum-for-positive-and-negative-numbers/36384094#36384094, for example. And charts behave different in some cases in a third way. But you can live with this if you respect some rules: 1. Don't trust more than 15 significant digits in Excel's numbers. 2. Not crying, rounding! – Axel Richter Apr 06 '16 at 15:15

1 Answers1

4

The article that you linked to is explicit about doing something nonstandard with values near 0:

Example when a value reaches zero 1.In Excel 95 or earlier, enter the following into a new workbook: A1: =1.333+1.225-1.333-1.225

2.Right-click cell A1, and then click Format Cells. On the Number tab, click Scientific under Category. Set the Decimal places to 15. Instead of displaying 0, Excel 95 displays -2.22044604925031E-16.

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.

The unspecified "optimization that attempts to correct for this problem" does mean that caution should be used in using Excel for numerical computations when strict agreement with IEEE 754 is required. Perhaps using VBA (which is unlikely to have this "optimization"?) might be a workaround.

John Coleman
  • 51,337
  • 7
  • 54
  • 119