Skip to content
Apiary

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 Infinite, NaN or denormal (or subnormal) numbers. This is good news for us, as JavaScript and Python also use double precision IEEE 754.

Our current strategy has been is to “look the same” but not exactly worry about the internals of this except where results deviate to a degree noticeable by regular users.

Output fidelity and 15-digit rounding

Section titled “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 15 significant digit limits output fidelity.

Despite no such limits existing in Excel number formatter, numbers are always rounded down before display and Excel will only ever displays the leading 15 figures.

=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

Trying to enter 8257.426484429656 into Excel will result in 8257.42648442965 (the final ..6 being chopped off).

While it seems clear that Excels precision is in most cases the same as it’s 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 Excels (effectively simulating Excels accrued precision errors).

Storage in to xlsx files

Section titled “Storage in to 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+B2)=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.

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


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

Something even stranger can be made to happen. Enter the number 2.000000000000014 in to 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>

Is a number not representable by IEEE 754. Clearly, something strange is happening in the last bits of the floats mantissa.

According to Wikipedia’s Numeric precision in Microsoft Excel:

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.

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

Relevant GRID issue:

Cancellations

Section titled “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.0000000000363797880709 (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.0000000000363797880709

What Excel does here, (contrary to the evidence in the 15-digit rounding section) is not round the output from the first operation, but evaluate if the combination of the raw radixes of the result and the operands exceed -48, and if so, switch the result to 0.

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

Relevant GRID issue:

Calculate as shown setting

Section titled “Calculate as shown setting”

Lastly it is worth mentioning there that Excel has an 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 work mentioning as a point of discussion into how complicated Excels number handling is.