Errors in formula calculation

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

"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

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