Errors in formula calculation

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

john.bedford3 wrote:
> 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

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

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
---
HTH,
David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001]
My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm
Search Page: http://www.mvps.org/dmcritchie/excel/search.htm

"john.bedford3" <john.bedford3@ntlworld.com> wrote ...
> 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

On Tue, 08 Feb 2005 11:16:27 GMT, "john.bedford3" <john.bedford3@ntlworld.com>
wrote:

>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

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

john.bedford3 wrote:

> 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

Hi Jerry,

Very nice representation of the excel limitations Well Done.

~Yogendra

"Ron Rosenfeld" <ronrosenfeld@nospam.org> wrote in message
news:hsch011807m8gtp583k4apvirqrmnptihu@4ax.com...
> On Tue, 08 Feb 2005 11:16:27 GMT, "john.bedford3"
<john.bedford3@ntlworld.com>
> wrote:
>
> >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

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

Thanks Jerry. It all becomes a lot clearer now.

John

"Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
news:4208BB8F.7090703@no_e-mail.com...
> 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
>
> john.bedford3 wrote:
>
> > 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
>

On Tue, 08 Feb 2005 13:58:07 GMT, "john.bedford3" <john.bedford3@ntlworld.com>
wrote:

>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

You're welcome.

Jerry

john.bedford3 wrote:

> Thanks Jerry. It all becomes a lot clearer now.
>
> John
>
> "Jerry W. Lewis" <post_a_reply@no_e-mail.com> wrote in message
> news:4208BB8F.7090703@no_e-mail.com...
>
>>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
>>
>>john.bedford3 wrote:
>>
>>
>>>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
>>>
>
>