Can you trust Numbers with numbers?

On 25 February 1991, 28 personnel were killed at a US military base in Saudi Arabia because of numerical inaccuracies which caused a Patriot air defence system to fail to detect an incoming Scud missile.

I’m not suggesting for a moment that you might be using Numbers spreadsheets for such critical tasks, but if you do any calculations in them, you need to have confidence that they’re not going to be inaccurate. This article reports the results of some tests of Numbers, Microsoft Excel and other software which you might use for these modest purposes.

Assessing accuracy of numerical calculations and causes of error is a huge subject. For guidance here I turned to Professor Nicholas Higham’s reference work (see below for details), and tried out some of his test cases.

Cancellation error

First up is the assessment of cancellation errors (section 1.7, if you’re following with the book), in which two almost equal numbers are subtracted from one another, resulting in a very small number. This example calculates the value of (1 - cos(x))/x^2, where x^2 means x squared. If x is 1.2E-05 (0.000012 in longhand), the result should be 0.5, which is correct to 10 significant figures. Software which uses inadequate approximation to the cosine function will be prone to cancellation error, and could return a result as incorrect as 0.6944, which isn’t even correct to one figure.

Prof Higham’s gold standard is the vast and expensive Matlab, which avoids the problems in what seems such a simple calculation. When set to its default format, Matlab returns 0.5000, but with format set to long, it gives 0.499999732974901. That’s almost the same when calculated in Swift, using Swift Playgrounds, which hedges its bets by giving both 0.5 and 0.4999997329749008.

numbers01

In Numbers, if you use that formula, you should see the result of 0.499999732974901 too, unless you perform the calculation step-by-step in separate cells. In that case, it should return the (almost) perfect result of 0.5. Whichever method you choose in Microsoft Excel, it too returns 0.499999732974901.

Rounding error

Next, I turn to rounding errors, which are tiny in themselves but are well known to accumulate or magnify until they have real impact on accuracy. For this, I used the well-known approximation for the mathematical constant e of (1 + 1/x)^x. As x tends towards infinity, this should come to a close approximation of e, that is around 2.71828… (Higham 1.11). When you start with x = 10, the approximation is poor, but should steadily improve until rounding error catches up with it.

Sure enough, in both Numbers and Excel the approximation came closest when x was 1E+08, then started to deteriorate until at x = 1E+15 the result was way out at 3.035. Results in the two spreadsheets are identical, and a reflection of the precision of the calculations being performed.

numbers02

Cancellation of rounding error

Rounding errors can cancel one another out, but there are circumstances in which they don’t, leading to grossly inaccurate results. To assess this, and gain a little insight into how Numbers handles a well-known tricky calculation, I computed (e^x - 1)/x (Higham 1.14.1). The direct and obvious way to calculate that normally suffers severe cancellation error as x falls far below 1, so I started with x = 1E-05 and descended to 1E-16.

numbers03

The correct answer here is around 1.00000005, which was reached at x = 1E-07. Then at x = 1E-16, cancellation error got the better of both Numbers and Excel, and they returned the result of 0. In reality, there’s another algorithm which you should prefer if you want to calculate this with such low values of x.

Accuracy

There are several tests of general numeric accuracy, of which I selected two, from Higham 2.11.

The first is to evaluate sin(22), which both Numbers and Excel computed accurately to 15 digits, as -8.85130929040388E-03.

The second is to evaluate 2.5^125, for which there’s both the direct method and that using EXP(125 * LN(2.5)). In Numbers, the direct method was again correct to 15 digits, at 5.52714787526044E+49, and the second to 14 digits, at 5.52714787526046E+49. What happened in Excel was surprising: it gave both as 6E+49, which looked alarmingly inaccurate until the number of digits shown was increased, when it showed them both correct to 14 digits.

Verdict

The accuracy of Numbers in mathematical calculations is at least as good as that of Microsoft Excel, and both are more than up to the job of performing these types of calculations.

Numbers has a novel formula editor which works well with simple formulae, but when they become more complex and need parentheses it becomes clumsy, and may require repeated editing to get a formula correct. If you need more complex formulae, it’s usually simpler to resort to editing them as text.

One important point about using any and all formulae in spreadsheets is the need for cross-check calculations to highlight any errors. Unlike mathematical programming environments such as Matlab, spreadsheets can readily return results which appear plausible but suffer severe errors, usually of the human kind. It’s very easy to add cross-checks to your spreadsheets which can reveal underlying errors. If you don’t, you will be caught out, hopefully not leading to failure of any air defence systems.

Note

In case you’re not familiar with the notation, 2E+02 is 2.0 x 10^2 = 200, and 2E-02 is 2.0 x 10^-2 = 0.02.

Reference

Nicholas J Higham (2002), Accuracy and Stability of Numerical Algorithms, 2nd edn, SIAM, ISBN 978 0 898715 21 7.