PS....

I wrote:
For example, suppose E1 is the constant 0.1, and E2 is =E1+2^-56.
[....]
E1: 0.100000000000000,00555111512312578270211815834045 41015625
E2: 0.100000000000000,01942890293094023945741355419158 935546875

Nonetheless, in Excel =E1=E2 displays TRUE.

In light of that, you might find it suprising that =E1-E2=0 displays FALSE
in this example. This is why I refer to the Excel remedial algorithm as
"half-baked".

But don't get me wrong: I am personally grateful for their half-baked
approach. It gives us an easy way to defeat it ;-).


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

"JoeU2004" wrote in message
...
I cannot explain your problem exactly because I cannot duplicate with the
example numbers that you provide. But I can explain the concepts.

First, numbers with decimal fractions usually cannot be represented
exactly in the internal form that Excel and most applications use (binary
floating point). For example, the following shows the constant entered on
the left and the exact internal value on the right. (The comma is my way
of demarcating the first 15 significant digits, which is all that Excel
will format.)

A1: 0.2003 0.200300000000000,00581756864903582027181982994079 58984375
A2: 0.0542 0.0541999999999999,9817923423961474327370524406433 10546875
A3: 0.0257 0.0257000000000000,0062172489379008766263723373413 0859375
A4: 0 0
A5: =sum(A1:A4) 0.2802
0.280200000000000,00461852778244065120816230773925 78125
A6: 0.2802
0.280200000000000,00461852778244065120816230773925 78125

As you can see, in this example, SUM(A1:A4) is indeed identical to the
constant 0.2802 internally.

We probably cannot duplicate your results because A1, A2, A3 and perhaps
even A4 (A1:D1 and A2:D2 in your posting) are probably not constants that
you entered. Instead, you are probably posting the displayed values that
result from formulas.

As is often the case then, displayed values are not exactly what they
appear to be, even when increase the number of decimal places.

So it would be prudent to write =ROUND(SUM(A1:A4),4). And I suspect that
will remedy your problem.

Just for fun, try =IF(10.1 - 10 = 0.1, TRUE). It will return FALSE(!).
But try =IF(ROUND(10.1 - 10,2) = 0.1, TRUE). It will return TRUE as
expected.

The reason is: 10.1 - 10 results in exactly
0.0999999999999996,4472863211994990706443786621093 75 , whereas the
constant 0.1 is exactly
0.100000000000000,00555111512312578270211815834045 41015625 .

In this case, you will see the difference if you format cells with those
expressions as Number with 16 decimal places.


When I run the following code I get the message box values ".2802",
".2802" and "False". The funny thing is...if I type a formula in the
worksheet =E1=E2 I get "True"

As noted above, both produce TRUE in this case when we use the constants
that you posted.

However, in general, the difference might arise because Excel employs some
(half-baked) heuristics to try to ameliorate the problem. Refer to the
section titled "Example When a Value Reaches Zero" in
http://support.microsoft.com/kb/78113/en-us .

For example, suppose E1 is the constant 0.1, and E2 is =E1+2^-56.

You will not see the difference using Excel; Excel displays
0.100000000000000 in both cases. Excel converts only the first 15
significant digits; that is, not counting the first 15 significant digits.
After that, it simply appends zeros.

But internally, the values are obviously different; to wit:

E1: 0.100000000000000,00555111512312578270211815834045 41015625
E2: 0.100000000000000,01942890293094023945741355419158 935546875

Nonetheless, in Excel =E1=E2 displays TRUE.

In VBA:

Msgbox Range("E1") = Range("E2")

displays FALSE because VBA does not implement any "remedial" heuristics.


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

"Steve" wrote in message
...
I have a problem in a macro where I'm comparing two values. They appear
to
be the same but when tested in the code, are different. I recreated the
problem using this scenario:

A1-D1 = .2003, .0542, .0257 and 0, respectively.
A2-D2 have the same values.

E1 = .2802
E2 = the formula sum(A2:D2)

When I run the following code I get the message box values ".2802",
".2802"
and "False". The funny thing is...if I type a formula in the worksheet
=E1=E2 I get "True":

Sub testit()
frst = Range("E1").Value
Scnd = Range("E2").Value
MsgBox frst
MsgBox Scnd
MsgBox (frst = Scnd)
End Sub