Oops, XL did it again

I really do love software that adheres to the "Law of Least Astonishment (LoLA)" (please see §4.1). Unfortunately, XL definitely does not belong to this group. Quite recently, I came across a violation of the LoLA by XL that is caused by the inconsistent manner of how functions evaluate numerical cells. MS have stated relatively clearly that the binary double-precision floating-point format (DP) is used internally in order to store numerical cell values. On the other hand, in the model-view-controller (MVC) of XL, i.e. a cell, a decimal string comprising at maximum 15 digits is used to display the numerical value of a cell to humans. To my great astonishment, it has turned out that different functions belonging to the same category in the official list of functions of XL2013 may operate on these two very different representations of numerical values. Of course, this can have severe consequences, as a precision of 15 decimal digits roughly corresponds to a precision of only 50 bits, whereas the 53 bits of precision of the IEEE754 DP format roughly correspond to 16 decimal digits.

I suppose virtually nobody regularly working with numbers will expect a cell formula like "= (D5 - FLOOR.PRECISE(D5;1))" to return a negative value in case the value of D5 is positive. In the official description of this function MS states: "Returns a number that is rounded down to the nearest integer or to the nearest multiple of significance. Regardless of the sign of the number, the number is rounded down." Moreover, the function FLOOR.PRECISE is listed in the "math and trigonometry" section of the aforementioned list, so that IMHO people will expect this function to be as accurate as the functions SQR and SIN are, for example. Yet, look what happens if the value of D5 is close to but still smaller than a positive integer. I’ll demonstrate that now in case of the integer 12 (twelve), which in DP big-endian byte order equals 40 28 00 00 00 00 00 00.

The hex representation of a numerical cell value as well as its equivalent exact decimal string were obtained by harnessing the commands "= Dbl2Hex(CellRef)" and "= DCStr(CellRef;100)", resp., which require an appropriate version of the FOSS add-in xNumbers to be installed in XL.

The largest DP value that is smaller than 12, but for which the XL2013x64 functions FLOOR, FLOOR.MATH, FLOOR.PRECISE, INT, ROUNDDOWN, and TRUNC return the mathematically correct result of 11 (eleven), has a hex notation of 40 27 FF FF FF FF FF E3. For all the other 28 DP values from 40 27 FF FF FF FF FF E4 up to 40 27 FF FF FF FF FF FF, the wrong value of 12 (twelve) is returned by all of these functions. Exactly for these 28 values the results of the formula "= (CellRef - FLOOR.PRECISE(CellRef;1))" turn out to be negative: about -4.97E-14 is returned by XL for the input corresponding to 40 27 FF FF FF FF FF E4, and about -1.776E-15 for 40 27 FF FF FF FF FF FF. If the mathematically unnecessary outer "magic parentheses" are omitted in the formula, XL tries to hide the dirty little secrets of its floating-point core from you by artificially zeroing the results for all values from 40 27 FF FF FF FF FF F9 to 40 27 FF FF FF FF FF FF. IMHO, both of these undocumented effects clearly exclude XL from the set of numerical software that might be considered for any serious computation (VBA is different!).

The observed results could be most easily explained by assuming that all these functions do not use the binary DP representation of the cell content in order to calculate their results, but operate on the numerical string comprising 15 decimal digits in the MVC instead. At least we have:
40 27 FF FF FF FF FF E3 hex =
XL: 1.19999999999999E+01
xN: 1.199999999999994848565…E+01
40 27 FF FF FF FF FF E4 hex =
XL: 1.20000000000000E+01
xN: 1.19999999999999502620…E+01
From the given xN values it can be seen that XL2013x64 correctly rounds the binary DP values to strings comprising 15 decimal digits, and that the functions in doubt obviously use this decimal string representation in order to calculate their result. Exactly the same kind of inaccurate results is delivered by the functions that are meant to round up, i.e. CEILING, CEILING.MATH, CEILING.PRECISE (and even ISO.CEILING), and ROUNDUP. In this example, all the 28 results corresponding to input values from 40 28 00 00 00 00 00 01 to 40 28 00 00 00 00 00 1C are flawed. Moreover, the functions ROUND and MROUND are also subject to this kind of flaw if their arguments are close to values at which their result should change.

On the other hand, the functions EVEN, MOD, ODD, and QUOTIENT deliver results that accurately account for the least bit of their arguments. Thus, in order to define an accurate version of the mathematical floor function, you might want to use = QUOTIENT(CellRef;1) + IF(MOD(CellRef;1)=0;0;(SIGN(CellRef)-1)/2), whereas for an accurate version of the mathematical ceiling function, you might want to use = QUOTIENT(CellRef;1) + 1 + IF(MOD(CellRef;1)=0;-1;(SIGN(CellRef)-1)/2). Or, you break free and look out for another numerically much more comprehensive programming language than VBA is, but that can be connected to XL relatively easy. At least, that is what I shall do from now on.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: