Search code examples
excelgoogle-sheetsieee-754floating-accuracy

Is Google Sheets less or more accurate than Microsoft Excel?

I am aware of the general problem of numerical inaccuracies with floating point numbers, but I would expect Excel and Google Sheets to behave the same. Unfortunately, they don't - see the following example:

A1: 15.525         our reference
A2: =3*5.175       should equal 15.525
A3: =A2=A1         shows TRUE, as expected, in both
A4: =A2-A1         but A2 is actually smaller in Google Sheets, by -1.78E-15 [Excel shows 0.00E+00]

B1: =ROUND(A1,2)   shows 15.53, correct, in both
B2: =ROUND(A2,2)   shows 15.53, correct, in both - surprisingly in Google Sheets, looking at A4
B3: =B2=B1         shows TRUE, as expected, in both

C1: =B1-A1         shows 0.00499999999999901, in both
C2: =B2-A2         shows 0.00500000000000078, in both
C3: =C2=C1         shows FALSE, as expected, in both

D1: =C1<0.005      consistent with C1, in both
D2: =C2>0.005      consistent with C2, but inconsistent with correct rounding in B2, in both

So what are the obvious problems?

  • A3: Google Sheets says that numbers which have a non-zero difference are equal (shudder)
  • A4: Excel shows a zero difference between numbers that behave differently (C1, C2, etc.)
  • B2/D2: both round up a number which is more than 0.005 smaller than the rounded number

My questions are:

  • Should I expect the two to behave differently?
  • Are differences an unintentional by-product of different implementations (number formats, maybe), or intentional?
  • Is this documented somewhere?
Solution

  • Observations

    The following observations were made on Google Sheets (web version on 2020-07-02 with Safari 12.1.2 on macOS 10.14.6), Microsoft Excel 2008 for Mac 12.3.6, and Numbers 10.0. In Excel, Preferences… > Calculation > “Set precision as displayed” was disabled.

    Cells were set:

    • A1: 15.525.
    • A2: =3*5.175.
    • A3: =A2=A1.
    • A4: =A2-A1.
    • A5: =(A2-A1).
    • A6: =A4*1000000000000000.
    • A7: =A5*1000000000000000.
    • A8: =(1/3*3-1)*POWER(10,34).

    The observed results were:

    • A3: “TRUE” in all three spreadsheets.
    • A4: “0” in all three spreadsheets.
    • A5: “0” in Google Sheets, “-1.77636E-15” in Excel, “0” in Numbers.
    • A6: “-1.776356839” in Google Sheets, “0” in Excel, “0” in Numbers.
    • A7: “-1.776356839” in Google Sheets, “-1.776356839” in Excel, “0” in Numbers.
    • A8: “0” in Google Sheets, “0” in Excel, “-1” in Numbers.

    If the cell formats are changed from Automatic to Scientific, then the Google Sheets results change:

    • A4: “-1.78E-15”.
    • A5: “-1.78E-15”.

    Otherwise, there are no changes in other cells or spreadsheets except cosmetic changes, such as showing “0.00E+00” instead of “0”.

    Hypotheses

    The above observations are consistent with:

    • Google Sheets uses IEEE-754 64-bit binary format and does not fudge the rounding. (The initial displays of “0” were due to Automatic formatting selecting a fixed-point or similar formatting.)
    • Excel uses IEEE-754 64-bit binary format and fudges the rounding depending on the formula used. That is, the rounding is not purely a function of the number being rounded; it is context dependent. Further, the rounding is not just for display; it is applied to the cell value, so further calculations use the post-rounding result.
    • Numbers uses a decimal floating-point format with 34 digits in the significand. No fudged rounding was observed.

    Further Investigation

    The −1.776356839•10−15 value observed in Google Sheets and Excel is characteristic of the IEEE-754 64-bit binary format (it is −2−49 up to the display precision), so it seems quite likely they are using that format. The −10−34 value observed in Numbers is less conclusive, so the hypothesis it is using a decimal format should be investigated further.

    The above notes rounding in Excel for numbers near zero, with the post-rounded value used in further calculations. Another experiment would be to construct a number that is not one but that displays as one yet has a simple formula and then use that cell in further calculations to see if the pre-rounding or post-rounding value is used.

    More Observations

    Cells were set:

    • B1: =1/49*49-1.
    • B2: =1/49*49.
    • B3: =B2-1.

    49 was chosen because, when evaluated with IEEE-754 binary64, 1/49*49 yields a number slightly under 1. (This does not occur with smaller denominators, such as with 1/3*3 because, although there is a rounding in the division, there is also a rounding in the multiplication that compensates, producing a result of exactly 1. 49 is the smallest integer for which this does not occur.)

    Results, with Scientific formatting:

    • B1: “-1.11E-16” in Google Sheets, “0.00E+00” in Excel, “-2E-34” in Numbers.
    • B2: “1.00E+00” in Google Sheets, “1.00E+00” in Excel, “1E+00” in Numbers.
    • B3: “-1.11E-16” in Google Sheets, “0E+00” in Excel, “0.00E+00” in Numbers.

    Again, Google Sheets seems to use IEEE-754 with no shenanigans. Excel appears to have rounded the results. However, if parentheses are added, setting B1 to =(1/49*49-1) or B3 to =(B2-1), Excel displays “-1.11E-16”. This is consistent with rounding being disabled when parentheses were used in the first set of observations.

    However, now we see some shenanigans in Numbers for the first time. We would expect B1 and B3 to show the same result, but they do not. Hypothesis: Numbers uses more precision within a formula but less for the final result of a cell?

    The B1 result is consistent with 34 decimal digits. To 34 significant decimal digits, 1/49 is .02040816326530612244897959183673469. Then 49 times that is .99999999999999999999999999999999981. Rounding to 34 significant digits gives .9999999999999999999999999999999998, and then subtracting 1 gives −2•10−34.

    Further experimentation, not detailed here, suggests Numbers calculates internally with 34 decimal digits but rounds the final cell value to 15 decimal digits and uses that for display and for further calculation.