Excel Calculation Errors

[Corundum]

I recently became aware that Excel does not calculate values exactly bu
only approximates the mathemetical value of equations. I needed t
develop a spreadsheet that tested if an experimental value was withi
certain limits and found that Excel frequently gave misleading result
due to the error in the approximations it was computing. I onl
discovered the source of the bug after expanding the calculated result
to their full decimal place as illustrated below:

Column B subtracted from Column A should always be 0.0616

A B A-B
1.0616 1 0.061600000000000100000000000000
2.0616 2 0.061599999999999900000000000000
3.0616 3 0.061599999999999900000000000000
4.0616 4 0.061600000000000300000000000000
5.0616 5 0.061600000000000300000000000000
6.0616 6 0.061600000000000300000000000000
7.0616 7 0.061600000000000300000000000000
8.0616 8 0.061600000000000300000000000000
9.0616 9 0.061600000000000300000000000000
10.0616 10 0.061600000000000300000000000000

when using the formula:

= if( (A-B)>= 0.0616, "PASS" , "FAIL" )

Values for B = 2 and 3 give erroneous results (as well as othe
combinations yielding 0.0616) .

I fixed the bug by adding a round() function to the equation but wa
disturbed by the inacuracy of Excel's calculations, even though i
occurs at 10 to the minus 16 decimal place.

Does anyone know how to better deal with this calculation flaw?

Thank

 

[Ron Rosenfeld]

I recently became aware that Excel does not calculate values exactly but
only approximates the mathemetical value of equations. I needed to
develop a spreadsheet that tested if an experimental value was within
certain limits and found that Excel frequently gave misleading results
due to the error in the approximations it was computing. I only
discovered the source of the bug after expanding the calculated results
to their full decimal place as illustrated below:

Column B subtracted from Column A should always be 0.0616

A B A-B
1.0616 1 0.061600000000000100000000000000
2.0616 2 0.061599999999999900000000000000
3.0616 3 0.061599999999999900000000000000
4.0616 4 0.061600000000000300000000000000
5.0616 5 0.061600000000000300000000000000
6.0616 6 0.061600000000000300000000000000
7.0616 7 0.061600000000000300000000000000
8.0616 8 0.061600000000000300000000000000
9.0616 9 0.061600000000000300000000000000
10.0616 10 0.061600000000000300000000000000

when using the formula:

= if( (A-B)>= 0.0616, "PASS" , "FAIL" )

Values for B = 2 and 3 give erroneous results (as well as other
combinations yielding 0.0616) .

I fixed the bug by adding a round() function to the equation but was
disturbed by the inacuracy of Excel's calculations, even though it
occurs at 10 to the minus 16 decimal place.

Does anyone know how to better deal with this calculation flaw?

Thanks

Excel's specifications include a maximum of 15 digits. This is common to most
spreadsheets that adhere to the IEEE convention, and is stated in the Excel
documentation.

If you require greater precision than 15 digits, you may be able to right a VBA
routine. It has a decimal data type that has the potential for 28 digit
precision, I believe. There is an Excel add-in that also allows extended
precision as well as other programs.

However, if fifteen digit precision is sufficient, then Rounding is as good a
way as any to handle the problem. You could also be specific about your error
tolerance and add in a small fudge factor (e.g. 10^-12)

Another issue has to do with the impossibility of representing certain numbers
in binary notation.


--ron

 

[Jerry W. Lewis]

It is not a bug. It is nature of computer calculations.

Excel (and almost all other hardware and software) works with binary
numbers. 0.0616 has no exact binary representation (much as 1/3 has no
exact decimal representation), and so must be approximated. The
approximation that Excel (and almost all other hardware and software)
uses is the IEEE standard for double precision, which is accurate to 15
decimal places (see Help for "Excel specifications and limits").
Consequently, Excel will only display 15 digits, and if you ask for more
than 15 digits, Excel merely pads the displayed value with zeroes.

The simplest way to think about this, is to consider everything past the
15th figure as unknown variation. Thus think of your second subtraction as
2.06160000000000??
-2.00000000000000??
--------------------
0.06160000000000??
for which Excel returns the consistent result of
0.0615999999999999

A more complicated explanation is that the closest double precision
binary approximation to 2.0616 is 4642310495893507/2251799813685248
which in decimal is
2.061599999999999877076106713502667844295501708984375
When you subtract 2 from it (integers can be represented exactly in
binary) you get exactly
0.061599999999999877076106713502667844295501708984375
which Excel correctly rounds to 15 digits for output display as
0.0615999999999999

The approximation to your input numbers was there all along, but was not
visible to you since Excel will not display more than 15 figures.
Subtraction merely revealed the approximation that was already there.
Rounding the result based on the number of reliable decimal places in
your inputs is perfectly valid.

Jerry

 

[Corundum]

Thanks for the input.

I was just confounded at first by seeing that sometimes the sam
apparent value evaluated as good and sometimes as bad

 

[Myrna Larson]

Hi, Ron:

Yes, you can use the decimal data subtype in VBA, but if you want to get the
entire number back into the worksheet, you have to pass it as text. You would
have to do all of your calculations in VB. Sort of defeates the purpose of the
spreadsheet, no?

 

[Ron Rosenfeld]

Hi, Ron:

Yes, you can use the decimal data subtype in VBA, but if you want to get the
entire number back into the worksheet, you have to pass it as text. You would
have to do all of your calculations in VB. Sort of defeates the purpose of the
spreadsheet, no?

That was only suggested (as one possible solution) if the OP needed more than
the 15 digit precision afforded by Excel.


--ron

 

[Jerry W. Lewis]

You are welcome.

Since f=0.0616 is a non-terminating binary fraction, what you are
actually working with is not the same in all your subtractions; hence
the different answers. The input value gets cut off at a different
point every time you cross a power of 2 boundary. Thus 1+f uses one
approximation, 2+f and 3+f use another, 4+f,5+f,6+f,7+f use another,
8+f,9+f,10+f...

Think of 1/3. If only 4 decimal figures were carried then, 1/3 ~
0.3333, 1+1/3 ~ 1.333, 10+1/3 ~ 10.33, etc., when you subtract off the
integer parts you get different answers because of the approximations to
1/3.

This may be more about binary than you ever wanted to know, but 0.0616
in binary is
0.0000111111000101000001001000000101101111000000000110100011...b
which is interpreted as 2^-5 +2^-6 +2^-7 +... IEEE double precision
only gives 53 binary figures ("bits", starting from the first 1), so
that in Double precision the approximation to 0.0616 rounds up to
0.000011111100010100000100100000010110111100000000011010010 b
the approximation to 1.0616 rounds up to
1.0000111111000101000001001000000101101111000000000111 b
so you get slightly more than 0.0616 after subtracting 1. The
approximation to 2.0616 rounds down to
10.000011111100010100000100100000010110111100000000011 b
so you get slightly less than 0.0616 after subtracting 2.

Jerry