Errors in formula calculation

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

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
>

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
>>>
>
>