Excel floating point arithmetic

Excel seems to use several strategies for throwing dust in our eyes (and those of its users) when it comes to floating point arithmetic.

Internally it uses the floating point standard in the world, double precision IEEE 754, but deviates from it by not implementing Infinity, NaN or denormal (or subnormal) numbers. This is good news for us, as JavaScript and Python also use double precision IEEE 754.

Our strategy has been to “look the same” but not exactly worry about the internals of this except where results deviate to a degree noticeable by regular users. Concretely, Apiary implements the two main compensation mechanisms described below: every numeric formula result is rounded to 15 significant digits, and the + and - operators apply Excel’s near-zero cancellation.

Output fidelity and 15-digit rounding

Double precision IEEE 754 is expected to be able to handle 15 to 17 significant digits of accuracy. However, Excel bends over backwards to try to maintain a 15-significant-digit limit on output fidelity.

Despite no such limits existing in the Excel number formatter, numbers are rounded to 15 significant figures for display and Excel will only ever display the leading 15 figures. The display rounding is to nearest, not truncation: =2/3 in a cell formatted to 15 decimal places displays 0.666666666666667. (Typed input is truncated rather than rounded, though — see below.)

=6717.22101348566 + 540.205470943996 - 8257.42648442966

The resulting number from this equation should be -1000.0000000000036 but Excel will display -1000. Comparing it to -1000 will also result in TRUE:

=(6717.22101348566 + 540.205470943996 - 8257.42648442966) = -1000

(Apiary matches the displayed result: since it rounds the final result of every formula to 15 significant digits, the first formula evaluates to -1000. But it does not round intermediate results; its = operator compares numbers with a small relative tolerance that covers only last-bit representation noise, not an accrued error as large as this example’s. So the comparison formula evaluates to FALSE in Apiary where Excel displays TRUE.)

Trying to enter 8257.426484429656 into Excel will result in 8257.42648442965 (the final ..6 being chopped off). This is general: Excel truncates typed numbers — including number literals in formulas — to 15 significant digits, and it truncates rather than rounding to nearest (entering 0.6666666666666666 yields 0.666666666666666, and 2.0000000000000071 yields exactly 2). So numeric input is truncated, while display output is rounded.

While it seems clear that Excel’s precision is in most cases the same as its output, this doesn’t always have to be the case, and we have reason to believe that Excel can maintain higher fidelity while running the formula (just as Apiary does). But contrary to that, we have also seen, while evaluating the output of power functions (like XNPV), that rounding numbers is sometimes needed to keep results the same as Excel’s (effectively simulating Excel’s accrued precision errors). Apiary does this in a few places, e.g. rounding the power terms in some financial functions.

Storage into xlsx files

Internally Excel’s representation of numbers is slightly more quirky than the above would indicate. Let’s give an example of a workbook:

A1 = 3300
B1 = 0.0000000000005
C1 = A1+B1          // => 3300
D1 = (A1+B1)=3300   // => TRUE

Opening the workbook to examine the content reveals that Excel saved the contents of cell C1 as 3300.0000000000005, a number representable by double precision IEEE 754. So while Excel’s engine works to a 15-significant-digit limit, saved xlsx files can contain 16-17-significant-digit values. Apiary does not truncate or round such values on read: the cell keeps the full IEEE 754 double parsed from the file, formula results get rounded to 15 significant digits as described above, and display formatting (the General number format) rounds to 15 significant digits, so such a cell still displays as 3300.

<c r="C1">
  <f>A1+B1</f>
  <v>3300.0000000000005</v>
</c>

<c r="D1" t="b">
  <f>(A1+B1)=3300</f>
  <v>1</v>
</c>

Something even stranger can be made to happen. Enter the number 2.000000000000014 into a cell — a number Excel does not consider equal to 2. It will try to display it as a 2 but can be coaxed into revealing the fraction, but only as 2.00000000000001. What it actually saves:

<c r="A1">
  <v>2.0000000000000102</v>
</c>

That looks like a value no IEEE 754 conversion should produce, but it is the 15-significant-digit input truncation at work: the typed 2.000000000000014 is truncated to 2.00000000000001, which is then stored as the nearest IEEE 754 double — and 2.0000000000000102 is exactly that double, written out with the up-to-17 significant digits the xlsx format uses. (A value that arrives without going through Excel’s input parsing — e.g. set through the automation API — keeps its full fidelity and saves as <v>2.0000000000000142</v>. Even the number literal in an entered formula =2.000000000000014 is truncated at entry: it saves as <f>2.00000000000001</f>.)

Wikipedia’s Numeric precision in Microsoft Excel describes this kind of observation in terms of result rounding:

Excel does some rounding and / or ‘snap to zero’ for most of its results, in average chopping the last 3 bits of the IEEE double representation.

But no mantissa-bit chopping is needed to explain the behaviour here; decimal input truncation accounts for it.

We ran an experiment where we generated numbers by shifting a bit through the raw mantissa at the exponent of +1:

   radix      mantissa
  ±rrrrrrrrrrrmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm
0b0100000000001000000000000000000000000000000000000000000000000000
0b0100000000000100000000000000000000000000000000000000000000000000
0b0100000000000010000000000000000000000000000000000000000000000000
0b0100000000000001000000000000000000000000000000000000000000000000
...
0b0100000000000000000000000000000000000000000000000000000000000010
0b0100000000000000000000000000000000000000000000000000000000000001

The lowest mantissa bit Excel would accept is in 2.000000000000014:

0b0100000000000000000000000000000000000000000000000000000000100000

Which, as shown above, it mangles. After that it converts all numbers to 2:

0b0100000000000000000000000000000000000000000000000000000000000000

This, too, is the input truncation: at magnitude 2, the mantissa bit worth 2^-46 (about 1.4E-14) is the lowest that still changes one of the first 15 significant decimal digits. Any smaller fraction is truncated away at entry, leaving exactly 2.

Cancellations

According to MS, and our investigations:

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.

In effect this means that after addition or subtraction operations, Excel will examine the result and throw it away if it is “trivial enough”.

An example of this:

=7717.22101348566 + 540.205470943996 - 8257.42648442966

With JS precision this yields an output of -0.0000000000036379788071 (which is representable by Excel) but Excel, however, emits 0 for this formula.

What is happening is this:

  7717.22101348566
+ 540.205470943996

# Excel = 8257.42648442966
# JS    = 8257.426484429656

Therefore, post addition, the final operation ends up as:

  8257.426484429656
- 8257.42648442966

# Excel = 0
# JS    = -0.0000000000036379788071

What Excel does here (contrary to the evidence in the 15-digit rounding section) is not round the output from the first operation, but compare the binary exponents (the raw radixes) of the result and its operands: when the result’s exponent is 50 or more below the operands’, the result is switched to 0. A bit-level sweep pins the threshold: =1 + 2^-49 - 1 survives as 1.78E-15 while =1 + 2^-50 - 1 yields 0, and at another magnitude =3300 + 2^-38 - 3300 survives while =3300 + 2^-39 - 3300 yields 0 — the same 50-binary-order gap below the operands (whose exponent there is 11).

Curiously, Excel has a built in “party trick” which will allow you to bypass cancellations:

=140737488355328 + 0.1 - 140737488355328
// output: 0

=(140737488355328 + 0.1 - 140737488355328)
// output: 0.09375

=1.333 + 1.225 - 1.333 - 1.225
// output: 0

=(1.333 + 1.225 - 1.333 - 1.225)
// output: -2.22045E-16

Apiary implements this cancellation: the + and - operators snap their result to 0 when its binary exponent is at least 50 below that of the first operand — the same threshold the sweep above shows Excel using. (The operands of a near-cancelling addition or subtraction necessarily share their exponent, so keying off the first operand is equivalent.) This matters beyond cosmetics: when a chain of additions and subtractions cancels to exactly 0 in Excel, a dependent formula like IF(X<=0, …) branches the same way in Apiary, instead of seeing a tiny nonzero residue. However, Apiary applies the cancellation unconditionally, so it does not reproduce the parenthesized bypass shown above: the parenthesized variants also evaluate to 0 in Apiary.

Calculate as shown setting

Lastly it is worth mentioning here that Excel has a preference setting: “Set precision as displayed”.

This option forces the value of each number in the worksheet to be at the precision that is displayed on the worksheet.

It is at this time unclear to us what this actually does. And we are unlikely to support it in the foreseeable future, but it is worth mentioning as an illustration of how complicated Excel’s number handling is.