Equal to zero ... or not equal to zero

Hi Excel friends,

Something interesting came up when I moved from Excel 2003 to 2007.

I have a sheet that looks as follows:

A B C D
E
1 51.71 51.71 =IF(A1=B1,"yes","no") =A1-B1
=IF(B1="fish",A1,A1-B1)

(you can substitute the "fish" with any text you like)

The results returned are as follows
C1 : yes (so they are the same)
D1 : 0 (which you would expect)

E1 : -7.10543E-15 (!!!)

A further test of =IF(E1=0,"zero","not zero") returns the result "not zero".

It seems to be that the value held in cell A1 is not quite equal to 51.71
from somewhere beyond the 15th decimal place (excel can't display it).

I can correct this with rounding on cell A1 but I wondered if anyone else
had seen this and if you know where I can read more. Honestly it is a bit of
a pain. The value in cell A1 was originally built up from summing elements
within a pivot table, then I copy-pasted values; however none of the elements
it is built from had values extending to more than two d.p. so I am having
trouble figuring out where the error comes in.

--
Allllen

What is the decimal place set for on that cell? Have you tried setting it to
zero?

"Allllen" wrote:

> Hi Excel friends,
>
> Something interesting came up when I moved from Excel 2003 to 2007.
>
> I have a sheet that looks as follows:
>
> A B C D
> E
> 1 51.71 51.71 =IF(A1=B1,"yes","no") =A1-B1
> =IF(B1="fish",A1,A1-B1)
>
> (you can substitute the "fish" with any text you like)
>
> The results returned are as follows
> C1 : yes (so they are the same)
> D1 : 0 (which you would expect)
>
> E1 : -7.10543E-15 (!!!)
>
> A further test of =IF(E1=0,"zero","not zero") returns the result "not zero".
>
> It seems to be that the value held in cell A1 is not quite equal to 51.71
> from somewhere beyond the 15th decimal place (excel can't display it).
>
> I can correct this with rounding on cell A1 but I wondered if anyone else
> had seen this and if you know where I can read more. Honestly it is a bit of
> a pain. The value in cell A1 was originally built up from summing elements
> within a pivot table, then I copy-pasted values; however none of the elements
> it is built from had values extending to more than two d.p. so I am having
> trouble figuring out where the error comes in.
>
> --
> Allllen