Numeric precision in Microsoft Excel: Difference between revisions

Revision as of 13:05, 15 April 2021

Revision as of 03:16, 22 June 2021

Line 1:

As with other spreadsheets, [[Microsoft Excel]] works only to limited accuracy because it retains only a certain number of figures to describe numbers (it has limited [[Arithmetic precision|precision]]). With some exceptions regarding erroneous values, infinities, and denormalized numbers, Excel calculates in [[double-precision floating-point format]] from the [[IEEE 754-2008|IEEE 754 specification]]<ref name=microsoft_spec>

{{cite web |url=http://support.microsoft.com/kb/78113/en-us |title=Floating-point arithmetic may give inaccurate results in Excel |publisher=Microsoft support |work=Revision 8.2 ; article ID: 78113 |date=June 30, 2010 |accessdateaccess-date=2010-07-02}}

</ref> (besides numbers, Excel uses a few other data types<ref name=Dalton>

Line 15:

==Accuracy and binary storage==

[[File:Excel fifteen figure.PNG|thumb|390px|Excel maintains 15 figures in its numbers, but they are not always accurate: mathematical the bottom line should be the same as the top line, in 'fp-math' the step '1 + 1/9000' leads to a rounding up as the first bit of the 14 bit tail '10111000110010' of the mantissa falling off the table when adding 1 is a '1', this uproundingup-rounding is not undone when subtracting the 1 again, since there is no information about the origin of the values in this step. Thus the 're-subtracting' of 1 leaves a mantissa ending in '100000000000000' instead of '010111000110010', representing a value of '1.1111111111117289E-4' rounded by ex$el to 15 significant digits: '1.11111111111173E-4'.]]

[[File:Excel errors.PNG|thumb|390px|Of course mathematical 1 + x − 1 = x, 'floating point math' is sometimes a little different, that's is not to be blamed on Excel. The discrepancy indicates the error. All errors are beyond the 15-th significant digit of the intermediate 1+x value, all errors are in high value digits of the final result, that's is the problematic effect of 'cancellation'.]]

In the top figure the fraction 1/9000 in Excel is displayed. Although this number has a decimal representation that is an infinite string of ones, Excel displays only the leading 15 figures. In the second line, the number one is added to the fraction, and again Excel displays only 15 figures. In the third line, one is subtracted from the sum using Excel. Because the sum has only eleven 1s after the decimal, the true difference when ‘1’ is subtracted is three 0s followed by a string of eleven 1s. However, the difference reported by Excel is three 0s followed by a 15-digit string of ''thirteen'' 1s and two extra erroneous digits. Thus, the numbers Excel calculates with are ''not'' the numbers that it displays. Moreover, the error in Excel's answer is not simply round-off error, it's is an effect in floating point calculations called 'cancellation'.

The inaccuracy in Excel calculations is more complicated than errors due to a precision of 15 significant figures. Excel's storage of numbers in binary format also affects its accuracy.<ref name=deLevie>

Line 23:

{{cite book |title=Advanced Excel for scientific data analysis |publisher=Oxford University Press |author=Robert de Levie |author-link=Robert de Levie |year=2004 |isbn=0-19-515275-1 |page=44 |chapter=Algorithmic accuracy |url=https://www.amazon.com/Advanced-Excel-Scientific-Data-Analysis/dp/0195152751/ref=sr_1_1?ie=UTF8&s=books&qid=1270770876&sr=1-1#reader_0195152751}}

</ref> To illustrate, the lower figure tabulates the simple addition {{nowrap|1 + ''x'' − 1}} for several values of ''x''. All the values of ''x'' begin at the 15-th decimal, so Excel must take them into account. Before calculating the sum 1 + ''x'', Excel first approximates ''x'' as a binary number. If this binary version of ''x'' is a simple power of 2, the 15-digit decimal approximation to ''x'' is stored in the sum, and the top two examples of the figure indicate recovery of ''x'' without error. In the third example, ''x'' is a more complicated binary number, ''x'' = 1.110111⋯111 × 2⁻⁴⁹ (15 bits altogether). Here the 'IEEE 754 double value' resulting of the 15 bit figure is 3.330560653658221E-15, which is rounded! by excelExcel for the 'user interface' to 15 digits 3.33056065365822E-15, and then displayed with 30 decimals digits gets one 'fake zero' added, thus the 'binary' and 'decimal' values in the sample are identical only in display, the values associated with the cells are different (1.1101111111111100000000000000000000000000000000000000 × 2⁻⁴⁹ vs. 1.1101111111111011111111111111111111111111111111111101 × 2⁻⁴⁹). Similar is done by other spreadsheets, the handling of the different amount of decimal digits which can be exactly stored in the 53 bit mantissa of a 'double' (e.g. 16 digits between 1 and 8, but only 15 between 0,5 and 1 and between 8 and 10) is somewhat difficult and solved 'suboptimal'. In the fourth example, ''x'' is a ''decimal'' number not equivalent to a simple binary (although it agrees with the binary of the third example to the precision displayed). The decimal input is approximated by a binary and then ''that'' decimal is used. These two middle examples in the figure show that some error is introduced.

The last two examples illustrate what happens if ''x'' is a rather small number. In the second from last example, ''x'' = 1.110111⋯111 × 2⁻⁵⁰; 15 bits altogether. The binary is replaced very crudely by a single power of 2 (in this example, 2⁻⁴⁹) and its decimal equivalent is used. In the bottom example, a decimal identical with the binary above to the precision shown, is nonetheless approximated differently from the binary, and is eliminated by truncation to 15 significant figures, making no contribution to {{nowrap|1 + ''x'' − 1}}, leading to ''x'' = 0.<ref name=decimal_input>

Line 55:

</ref>

1. The shortcomings in the '= 1 + x - 1' tasks are a combination of 'fp-math weaknesses' and 'how excelExcel handles it', especially excelsExcel's rounding. Excel does some rounding and / or 'snap to zero' for most of it'sits results, in average chopping the last 3 bits of the IEEE double representation. This behavior can be switched of by setting the formula in parentheses: '= ( 1 + 2^-52 - 1 )'. You will see that even that small value survives. Smaller values will pass away as there are only 53 bits to represent the value, for this case 1.0000000000 0000000000 0000000000 0000000000 0000000000 01, the first representing the '1', and the last the '2^-52'.

2. It's is not only clean powers of two surviving, but any combination of values constructed of bits which will be within the 53 bits once the decimal 1 is added. As most decimal values don'tdo not have a clean finite representation in binary they will suffer from 'round off' and 'cancellation' in tasks like the above.

E.g. decimal 0,1 has the IEEE double representation 0 (1).1001100110011001100110011001100110011001100110011010 times 2^-4, and added to 140737488355328.0 (which is 2^47) will looselose all of it'sits bits except the first two. Thus from '= ( 140737488355328.0 + 0,1 - 140737488355328.0) it will come back as 0.09375 calculated with www.weitz.de/ieee (64 bit) as well as in excelExcel with the parentheses around the formula. This effect mostly can be managed by meaningful rounding, which excelExcel doesn'tdoes not apply, it's is up to the user.

Needless to say: other spreadsheets have similar problems, LibreOffice Calc uses a more aggressive rounding, while gnumeric tries to keep precision and make as well the precision as the 'lack of' visible for the user.

Line 103:

but displays <math>-3.55271E-15</math> instead.

(The above is not limited to subtractions, try '=1+1,405*2^-48' in one cell, excelExcel rounds the display to 1,00000000000000000000, and '=0,9+225179982494413*2^-51' in another, same display (in the range above 1 / below 1 the rounding is different, which hits most decimal or binary magnitude changes). above, different rounding for value and display, violates one of the elementary requirements in [https://docs.oracle.com/cd/E19957-01/806-3568/ncg_goldberg.html Goldberg: 'What Every Computer Scientist Should Know About Floating-Point Arithmetic'] (more or less 'the holy book' of fp-math), there stated: '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 excelExcel, e.g. LibreOffice calc acts similarsimilarly).

===Round-off error===