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 stores a formula’s result as the double it computed, as Excel does, and implements the two main compensation mechanisms described below: the comparison operators compare numbers rounded to 15 significant digits, and, outside Google Sheets mode, a + or - at the root of a formula applies Excel’s near-zero cancellation. Like Excel, it also reads a subnormal product or quotient (nonzero, below 2^-1022 in magnitude) as 0, whether computed by *, /, % or ^, or inside a function such as PRODUCT, POWER, MMULT, AVERAGE, SUMPRODUCT, SUMSQ, ROUND, CONVERT, MDETERM or MINVERSE, except in Google Sheets mode, which keeps it as Google Sheets does: =1E-300*1E-14*1E+14 is 0 in Excel mode, not about 1E-300. +, -, SUM and the sums inside SUMPRODUCT and MMULT keep a subnormal result, and so does the cell; an xlsx export writes it as 0, as Excel saves it.
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 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.42648442966The 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 stores the first formula’s result as -1000.0000000000036, as Excel does, and the General number format displays it as -1000. Its comparison operators, and the number criteria of COUNTIF, SUMIF and their kin, compare numbers rounded to 15 significant digits, as Excel’s do, so the comparison formula evaluates to TRUE. In Google Sheets mode they round as Google Sheets does, which for a few numbers lands one unit away from Excel’s 15th digit, and the comparison evaluates to TRUE there too.)
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
Section titled “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 = 3300B1 = 0.0000000000005C1 = A1+B1 // => 3300D1 = (A1+B1)=3300 // => TRUEOpening 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, nor formula results: the cell keeps the full IEEE 754 double, parsed from the file or computed by its formula, 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 ±rrrrrrrrrrrmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmmm0b01000000000010000000000000000000000000000000000000000000000000000b01000000000001000000000000000000000000000000000000000000000000000b01000000000000100000000000000000000000000000000000000000000000000b0100000000000001000000000000000000000000000000000000000000000000...0b01000000000000000000000000000000000000000000000000000000000000100b0100000000000000000000000000000000000000000000000000000000000001The lowest mantissa bit Excel would accept is in 2.000000000000014:
0b0100000000000000000000000000000000000000000000000000000000100000Which, as shown above, it mangles. After that it converts all numbers to 2:
0b0100000000000000000000000000000000000000000000000000000000000000This, 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
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”. The test looks only at how small the result is, not at whether any conversion error occurred: =(2^53-9)-(2^53-10) is 0 in Excel, although both operands and their difference of 1 are exactly representable.
An example of this:
=7717.22101348566 + 540.205470943996 - 8257.42648442966With 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.426484429656Therefore, post addition, the final operation ends up as:
8257.426484429656- 8257.42648442966
# Excel = 0# JS = -0.0000000000036379788071What 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-16Excel applies the cancellation only to the operation at the root of an expression: a + or - that nothing else in a cell formula, a defined name’s formula or a LAMBDA body encloses, not even a redundant pair of parentheses (which is how the party trick above works). Every + and - below the root keeps its raw IEEE 754 result, so =1+2^-52-1 is 0 while =1+2^-52-1=0 is FALSE, and =1+2^-52-1+0, =SUM(1+2^-52-1) and =2^52*(1+2^-52-1) keep the residue (2.22E-16, 2.22E-16 and 1). A LET binding or body is not a root, so =LET(x,1+2^-52-1,x) keeps the residue too.
Apiary does the same: a root + or - snaps its result to 0 when its binary exponent is at least 50 below that of the left operand (the threshold the sweep above shows Excel using), and every + or - below the root computes the raw result. Excel too takes the threshold from the left operand: in =70368744177664-0.1-70368744177664, whose operands straddle a power of two, the left operand’s exponent decides, and the residue of -0.1015625 is kept. 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’s parser discards grouping parentheses, so the parenthesized variants above also evaluate to 0 in Apiary. In Google Sheets mode no + or - cancels, as Google Sheets keeps the result.
Calculate as shown setting
Section titled “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.