PS#2....
I wrote:
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" 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