compare values

[Steve]

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(A22)

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

 

[Pete_UK]

Increase the number of decimal places shown for cells E1 and E2 and
you will see that they are not exactly equal. This is a common problem
with computers which use binary numbers to represent decimals - some
decimal fractions cannot be represented exactly with binary fractions,
and so rounding errors occur.

I'm sure that JoeU will give a more detailed explanation.

Hope this helps.

Pete

 

[Jim Thomlinson]

Your code works fine for me. I get True as the final msgbox.

 

[Atishoo]

Works fine for me too! Are you running it with the right worksheet? stick
with worksheets ("your worksheet name") and end with either side of your sub
to be sure! otherwise cant figure it!

 

[Steve]

I increased to 123 decimal places and there are no differences.

 

[Steve]

I'm really happy for you but it doesn't really help me much does it?

 

[Jim Thomlinson]

What that tells me is there has to be more to this than what you have posted.
Are there other formulas involved. Is your code copeid exactly. Is there more
to the code... Since you are obviously not using option explicit and
declaring variable spelling counts so if you have retyped the code for your
question then you need to review that.

Perhpas try opening a new workbook and following your question exactly as
posted and let us know how that goes...

 

[JoeU2004]

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,0058175686490358202718198299407958984375
A2: 0.0542 0.0541999999999999,981792342396147432737052440643310546875
A3: 0.0257 0.0257000000000000,00621724893790087662637233734130859375
A4: 0 0
A5: =sum(A1:A4) 0.2802
0.280200000000000,0046185277824406512081623077392578125
A6: 0.2802
0.280200000000000,0046185277824406512081623077392578125

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 (A11 and A22 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,447286321199499070644378662109375 , whereas the constant
0.1 is exactly 0.100000000000000,0055511151231257827021181583404541015625 .

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,0055511151231257827021181583404541015625
E2: 0.100000000000000,01942890293094023945741355419158935546875

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

 

[JoeU2004]

PS....

For example, suppose E1 is the constant 0.1, and E2 is =E1+2^-56.
[....]
E1: 0.100000000000000,0055511151231257827021181583404541015625
E2: 0.100000000000000,01942890293094023945741355419158935546875

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]

PS#2....

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

Or if you would prefer to retain the exact values in E1 and E2, you could
write:

Excel:
=ROUND(E1,4)=ROUND(E2,4)

VBA:
Msgbox WorksheetFunction.Round(Range("E1"),4) = _
WorksheetFunction.Round(Range("E2"),4) = _

Note that I use WorksheetFunction.Round, not the VBA Round() function. The
reason is: the VBA implements "banker's rounding", which differs from
"normal" (Excel) rounding. In VBA, compare Round(2.5,0) and
WorksheetFunction.Round(2.5,0).


Also, if you are mixing Excel and VBA arithmetic involving numbers with
decimal fractions, one final comment....

There may be circumstances where a complex expression in Excel (more than
two terms) does not compare exactly equal with the same complex expression
in VBA. Here is one example.


In VBA:

Sub doit()
Range("a2") = (2226 - 123.36 - 39.34 - 303 - 1600) * 0.05
End Sub


In Excel:

A1: =(2226 - 123.36 - 39.34 - 303 - 1600) * 0.05
A3: =A1=A2


A3 will display FALSE(!). The reason is: A1 is
8.01499999999998,63575794734060764312744140625 internally, whereas A2 is
8.01500000000000,05684341886080801486968994140625. You will see a
difference even if you format A1 and A2 as Number with 14 decimal places.

The reason that Excel and VBA have slightly different results has to do with
the way each does arithmetic.

Excel does pairwise operations (e.g. first 2226 minus 123.36, then that
result minus 39.34, etc), converting to a 64-bit floating point
representation for each operation.

VBA does all the operations in the higher-precision 80-bit floating point
representation provided by Intel-compatible CPUs, converting only the final
result to 64-floating point. (Unless the expression contains function
calls.)

Which reminds me: I should mention that while these concepts are true for
Excel and VBA on all binary CPUs, the specific examples and their results
apply to Intel-compatible CPUs. I am not familiar with the Mac.


Again, the proper and necessary workaround to all of these numerical
"aberrations" is to use "fuzzy logic" when comparing floating points results
(i.e. numbers with decimal fractions), no matter how benign the calculations
might look. By "fuzzy logic", I mean either explicit rounding or something
of the form ABS(A1-A2)<0.0001 to test for "equality".


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

 

[JoeU2004]

Errata (typo)....

Excel converts only the first 15 significant digits; that is,
not counting the first 15 significant digits.

Huh?!

I cannot tell you how many times I reread that sentence, but only now do I
see the typo. It should be:

Excel converts only the first 15 significant digits; that is, not counting
leading zeros.

Klunk!


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