Statistical functions

Error in Excel 2007 calculation of standard deviation. All four columns have the same deviation of 0.5

Accuracy in Excel-provided functions can be an issue. Altman et al. (2004) provide this example:^[7] The population standard deviation given by:

${\displaystyle {\sqrt {\frac {\Sigma (x-{\bar {x}})^{2}}{n}}}={\sqrt {\frac {\Sigma \left[x-\left(\Sigma x\right)/n\right]^{2}}{n}}}\ ,}$

is mathematically equivalent to:

${\displaystyle {\sqrt {\frac {n\Sigma x^{2}-\left(\Sigma x\right)^{2}}{n^{2}}}}\ .}$

However, the first form keeps better numerical accuracy for large values of x, because squares of differences between x and x leads to less round-off than the differences between the much larger numbers Σ(x²) and (Σx)² . The built-in Excel function STDEVP, however, uses the less accurate formulation because it is faster computationally.^[5]

Both the "compatibility" function STDEVP and the "consistency" function STDEV.P in Excel 2010 return the 0.5 population standard deviation for the given set of values. However, numerical inaccuracy still can be shown using this example by extending the existing figure to include 10¹⁵, whereupon the erroneous standard deviation found by Excel 2010 will be zero.

Subtraction of Subtraction Results

Doing simple subtractions may lead to errors as two cells may display the same numeric value while storing two separate values. An example of this occurs in a sheet where the following cells are set to the following numeric values:

${\displaystyle A1:=28.552}$

${\displaystyle A2:=27.399}$

${\displaystyle A3:=26.246}$

and the following cells contain the following formulas

${\displaystyle B1:=A1-A2}$

${\displaystyle B2:=A2-A3}$

Both cells ${\displaystyle B1}$ and ${\displaystyle B2}$ display ${\displaystyle 1.1530}$. However, if cell ${\displaystyle C1}$ contains the formula ${\displaystyle B1-B2}$ then ${\displaystyle C1}$ does not display ${\displaystyle 0}$ as would be expected, but displays ${\displaystyle -3.55271E-15}$ instead.

The above is not limited to subtractions, try = 1 + 1.405*2^(-48) in one cell, Excel rounds the display to 1,00000000000000000000, and = 0.9 + 225179982494413×2^(-51) in another, same display^{[lower-alpha 4]} above, different rounding for value and display, violates one of the elementary requirements in Goldberg (1991)^[8] who states:

... 'it is important to make sure that its use is transparent to the user. For example, on a calculator, if the internal representation of a displayed value is not rounded to the same precision as the display, then the result of further operations will depend on the hidden digits and appear unpredictable to the user' ...

The problem is not limited to Excel, e.g. LibreOffice calc acts similarly.

Round-off error

User computations must be carefully organized to ensure round-off error does not become an issue. An example occurs in solving a quadratic equation:

${\displaystyle ax^{2}+bx+c=0\ .}$

The solutions (the roots) of this equation are exactly determined by the quadratic formula:

${\displaystyle x={\frac {-b\pm {\sqrt {b^{2}-4ac}}}{2a}}.}$

When one of these roots is very large compared to the other, that is, when the square root is close to the value b, the evaluation of the root corresponding to subtraction of the two terms becomes very inaccurate due to round-off (cancellation?).

It is possible to determine the round-off error by using the Taylor series formula for the square root: ^[9]

${\displaystyle {\sqrt {b^{2}-4ac}}=b\ {\sqrt {1-{\frac {4ac}{b^{2}}}}}\approx b\left(1-{\frac {2ac}{b^{2}}}+{\frac {2a^{2}c^{2}}{b^{4}}}+\cdots \right).}$

Consequently,

${\displaystyle b-{\sqrt {b^{2}-4ac}}\approx b\left({\frac {2ac}{b^{2}}}-{\frac {2a^{2}c^{2}}{b^{4}}}+\cdots \right),}$

indicating that, as b becomes larger, the first surviving term, say ε:

${\displaystyle \varepsilon ={\frac {2ac}{b}},}$

becomes smaller and smaller. The numbers for b and the square root become nearly the same, and the difference becomes small:

${\displaystyle b-{\sqrt {b^{2}-4ac}}\approx b-b+\varepsilon .}$

Under these circumstances, all the significant figures go into expressing b. For example, if the precision is 15 figures, and these two numbers, b and the square root, are the same to 15 figures, the difference will be zero instead of the difference ε.

A better accuracy can be obtained from a different approach, outlined below.^{[lower-alpha 5]} If we denote the two roots by r ₁ and r ₂, the quadratic equation can be written:

${\displaystyle \left(x-r_{1}\right)\left(x-r_{2}\right)=x^{2}-\left(r_{1}+r_{2}\right)x+r_{1}\ r_{2}=0.}$

When the root r ₁ >> r ₂, the sum (r ₁ + r ₂ ) ≈ r ₁ and comparison of the two forms shows approximately:

${\displaystyle r_{1}\approx -{\frac {b}{a}},}$

while

${\displaystyle r_{1}\ r_{2}={\frac {c}{a}}.}$

Thus, we find the approximate form:

${\displaystyle r_{2}={\frac {c}{a\ r_{1}}}\approx -{\frac {c}{b}}.}$

These results are not subject to round-off error, but they are not accurate unless b² is large compared to ac.

Excel graph of the difference between two evaluations of the smallest root of a quadratic: direct evaluation using the quadratic formula (accurate at smaller b) and an approximation for widely spaced roots (accurate for larger b). The difference reaches a minimum at the large dots, and round-off causes squiggles in the curves beyond this minimum.

The bottom line is that in doing this calculation using Excel, as the roots become farther apart in value, the method of calculation will have to switch from direct evaluation of the quadratic formula to some other method so as to limit round-off error. The point to switch methods varies according to the size of coefficients a and b.

In the figure, Excel is used to find the smallest root of the quadratic equation x² + bx + c = 0 for c = 4 and c = 4 × 10⁵. The difference between direct evaluation using the quadratic formula and the approximation described above for widely spaced roots is plotted vs. b. Initially the difference between the methods declines because the widely spaced root method becomes more accurate at larger b-values. However, beyond some b-value the difference increases because the quadratic formula (good for smaller b-values) becomes worse due to round-off, while the widely spaced root method (good for large b-values) continues to improve. The point to switch methods is indicated by large dots, and is larger for larger c-values. At large b-values, the upward sloping curve is Excel's round-off error in the quadratic formula, whose erratic behavior causes the curves to squiggle.

A different field where accuracy is an issue is the area of numerical computing of integrals and the solution of differential equations. Examples are Simpson's rule, the Runge–Kutta method, and the Numerov algorithm for the Schrödinger equation.^[10] Using Visual Basic for Applications, any of these methods can be implemented in Excel. Numerical methods use a grid where functions are evaluated. The functions may be interpolated between grid points or extrapolated to locate adjacent grid points. These formulas involve comparisons of adjacent values. If the grid is spaced very finely, round-off error will occur, and the less the precision used, the worse the round-off error. If spaced widely, accuracy will suffer. If the numerical procedure is thought of as a feedback system, this calculation noise may be viewed as a signal that is applied to the system, which will lead to instability unless the system is carefully designed.^[11]

Accuracy within VBA

Although Excel nominally works with 8-byte numbers by default, VBA has a variety of data types. The Double data type is 8 bytes, the Integer data type is 2 bytes, and the general purpose 16 byte Variant data type can be converted to a 12 byte Decimal data type using the VBA conversion function CDec.^[12] Choice of variable types in a VBA calculation involves consideration of storage requirements, accuracy and speed.