"David Biddulph" <groups [at] biddulph.org.uk wrote:
Excel 2007 has different ways of guessing whether a
difference is zero. It's one of its known problems,
and you'll see frequent reports in the archives of
this and other excel newsgroups.

Oh? I do not remember seeing any threads where the root cause was found to
be a difference in the way that Excel 2007 interprets arithmetic results.
Can you point me to one, for my edification?

I do remember many threads related to some functions (XIRR, NPV, YIELD, even
EDATE!) returning errors reportedly in Excel 2007, but not in Excel 2003,
allegedly with no changes other than to open the workbook in Excel 2007. I
do not believe the root cause was ever agreed upon. For example, see
http://www.google.com/url?url=http:/...0NeOfRxIe2h2jg .

Is that you're thinking of?

I'm not trying to impugn your statement. I'm just very curious about this,
having spent a lot of time reverse-engineering the dubious heuristic as it
is implemented in Excel 2003.


----- original message -----

"David Biddulph" <groups [at] biddulph.org.uk wrote in message
...
Excel 2007 has different ways of guessing whether a difference is zero.
It's one of its known problems, and you'll see frequent reports in the
archives of this and other excel newsgroups.
--
David Biddulph

"Gee" wrote in message
...
OK, all that said and my subsequent confusion, I never had a problem with
this kind of thing in '03, only in '07 and I'm still working on another
problem with it but the 13 decimal places solved the Yes/No problem just
fine.
Thanks so much for your help.

...

"JoeU2004" wrote:
...
But using ROUND in comparisons with numbers with decimal fractions is
okay,
when done correctly. For example:

=if(and(400.5<=round(HI2,1),round(HI2,1)<402.5), "YES", "NO")

This is okay because ROUND ensures that the internal binary
representation
of its result exactly matches a constant with the same number of decimal
places. For example, if A1 is =10.1-10, =(A1=0.1) returns FALSE, but
=(round(A1,1)=0.1) returns TRUE. The reason is: the binary
representation
of 10.1-10 is not close enough to the binary representation of the
constant
0.1 for Excel to consider them equal.

In contrast, if A1 is =0.1+2^-56, =(A1=0.1) returns TRUE even though the
binary representations of A1 and 0.1 are not the same. Likewise,
=A1-0.1
returns zero. But =(A1-0.1) returns non-zero, namely about 1.39E-17.
And
=IF(A1-0.1=0,TRUE) returns FALSE, which flies in the face of reason
considering the result of =A1-0.1, until we understand Excel's dubious
heuristic for determining "close enough".
...