Errors in formula calculation

[john.bedford3]

I have been having problems with incorrect calculation of column totals when
using the SUM function. The data I have entered in the column to 20 decimal
places are as follows: 7.4, 10,14.7 and -32. These should total 0.1 but
excel calculates the total as 0.09999999999999430000

I am using Excel97 does anyone else have similar problems and does this also
occur with later versions of Excel.

Thank you.

John

 

[yogendra.joshi]

I have been having problems with incorrect calculation of column totals when
using the SUM function. The data I have entered in the column to 20 decimal
places are as follows: 7.4, 10,14.7 and -32. These should total 0.1 but
excel calculates the total as 0.09999999999999430000

I am using Excel97 does anyone else have similar problems and does this also
occur with later versions of Excel.

Thank you.

John

 

[yogendra.joshi]

Hi John,

Looks like the problem is with the numbers as displayed and as stored.

Try changing the format to number, and you will see if there are any
additional decimal digits that excel has stored.

If you need further help, try sending the file to me.
HTH

Yogendra

 

[David McRitchie]

Hi John,
There is no error.

You can't enter numbers with 20 significant digits.
See "Specification Limits" in HELP.

Calculation specifications
Feature Maximum limit
Number precision 15 digits

 

[Ron Rosenfeld]

I have been having problems with incorrect calculation of column totals when
using the SUM function. The data I have entered in the column to 20 decimal
places are as follows: 7.4, 10,14.7 and -32. These should total 0.1 but
excel calculates the total as 0.09999999999999430000

I am using Excel97 does anyone else have similar problems and does this also
occur with later versions of Excel.

Thank you.

John

I believe you are misunderstanding certain characteristics of Excel (and other
spreadsheet programs that use the IEEE specification).

1. Excel only has 15 digit precision.
2. I don't understand how you enter a number like 10 to 20 decimal places.
What difference is there between 10 and 10.00000000000000000000? If you enter
either, and look in the formula bar, you will see just "10" (without quotes)???
3. Excel will convert numeric entries to binary. Some decimal numbers cannot
be represented with a finite binary number. (Much like 1/3 cannot be
represented exactly in base 10 -- 0.33333333333...)

Both 7.4 and 14.7 would require an infinite length number to be
represented in binary.

There are several possible workarounds. Rounding or using "precision as
displayed" are two possibilities. BUT, if you truly need 20 digit precision
for scientific purposes, Excel may not be the program to use.

See http://www.cpearson.com/excel/rounding.htm for a fuller discussion.


--ron

 

[Jerry W. Lewis]

Most decimal fractions (including .4, .7, and .1) have no exact binary
representation, and hence must be approximated. The calculations are
performed correctly based on those approximate inputs. You also will
not get exactly 0.1 from =32.1-32 for the same reason. Moreover, you
will get similar results with every software program that that does
binary math (all versions of Excel, and almost all other general purpose
computing software).

Since the issue is approximation of inputs rather than subsequent
calculations, you can clearly round the final result without concern.

An easy way to understand the issue is to think of the issues with
approximating 1/3 in decimal. In a hypothetical decimal computer that
carries 4 digits, (32+1/3)-32 = 32.33-32 = 0.33, which does not equal
the 4 digit approximation to 1/3.

An easy way to predict the potential magnitude of approximations is to
consider the documented (Help for "Excel specifications and limits"
subtopic "Calculation specifications") limit of 15 decimal digit
accuracy. Your problem then becomes
7.40000000000000??
10
+14.7000000000000???
--------------------
32.1000000000000???
-32
--------------------
0.1000000000000???
which agrees with Excel's representation
0.0999999999999943
of the exact result
0.099999999999994315658113919198513031005859375
based on IEEE approximations to your inputs.

Jerry

 

[yogendra.joshi]

Hi Jerry,

Very nice representation of the excel limitations Well Done.

~Yogendra

 

[john.bedford3]

I believe you are misunderstanding certain characteristics of Excel (and other
spreadsheet programs that use the IEEE specification).

1. Excel only has 15 digit precision.
2. I don't understand how you enter a number like 10 to 20 decimal places.
What difference is there between 10 and 10.00000000000000000000? If you enter
either, and look in the formula bar, you will see just "10" (without quotes)???
3. Excel will convert numeric entries to binary. Some decimal numbers cannot
be represented with a finite binary number. (Much like 1/3 cannot be
represented exactly in base 10 -- 0.33333333333...)

Both 7.4 and 14.7 would require an infinite length number to be
represented in binary.

There are several possible workarounds. Rounding or using "precision as
displayed" are two possibilities. BUT, if you truly need 20 digit precision
for scientific purposes, Excel may not be the program to use.

See http://www.cpearson.com/excel/rounding.htm for a fuller discussion.


--ron

I am sorry I did not make it clear in my original post. The numbers were not
entered to 20 decimal places but I expanded them to 20 decimal places to
see what excel had done to the calculation. The answer excel gives is to 16
decimal places where simple mental arithmetic should show the answer to be
0.1.

Is this simply because it is converting to binary and back to decimal?

If I attempt to use precision as displayed a warning comes up that data will
permanently lose accuracy. Yet it is inaccuracy of the calculation I am
trying to get rid of.

I have now tried using ROUND and this appears to solve the problem although
I did not think of that before as simple addition does not indicate that
there should be more than one decimal place in the answer.

Hopefully this has solved my problem.

Thank you for your help

John

 

[john.bedford3]

Thanks Jerry. It all becomes a lot clearer now.

John

 

[Ron Rosenfeld]

Is this simply because it is converting to binary and back to decimal?

If I attempt to use precision as displayed a warning comes up that data will
permanently lose accuracy. Yet it is inaccuracy of the calculation I am
trying to get rid of.

I have now tried using ROUND and this appears to solve the problem although
I did not think of that before as simple addition does not indicate that
there should be more than one decimal place in the answer.

Hopefully this has solved my problem.

Thank you for your help

I'm glad you have solved your problem. And Lewis has given a much clearer
explanation of the issues.


--ron

 

[Jerry W. Lewis]

You're welcome.

Jerry

Thanks Jerry. It all becomes a lot clearer now.

John