0.0571428571428571 = 5.17428571428571E-02
They are one and the same number, are they not?

Loz

-----Original Message-----
OK, what am I missing here.

Using XL2K3 XP, no problems with my computer, trying to
divide a series of
numbers by 100, then multiplying that quotient by 100,
and getting illogical
results.

Example:

If you take a pencil and paper, or any decent calculator,
and calculate the
formula
100 divided by 175,000 times 100
the result should be 0.0571428571428571

If you enter 100 in A1, 175,000 in B1, and 100 in C1
and in D1 enter the formula =A1/B1*C1
Then D1 returns the same result of 0.0571428571428571
if D1 is formatted for 16 decimal places.

The problem is I need to do this calculation
programmatically and the result
through VBA is incorrectly calculated at several hundred
times more than
what the correct result should be.

Example:
Given the above values in A1:C1, this codeline
MsgBox Range("A1").Value / Range("B1").Value * Range
("C1").Value
displays "5.17428571428571E-02"

and a Message Box will display that same result given
MsgBox Range("D1").Value
whether the formula resides in cell D1, or D1 has been
copied and paste
special'd for values.

The same bad result is obtained in the Immediate Window,
entering
? Range("A1").Value / Range("B1").Value * Range
("C1").Value

If I use a numerator less than or equal to 98, or greater
than or equal to
175, I get expected results from the calculation.
The problem is, the 100 numerator needs to be a static
100, and so does the
other 100 multiplicand.
I am developing a project that requires this calculation
for a dozen or so
denominator values (175,000 being one), and I get the
same strange results
for all denominator values (240,000; 333,701; 350,000 as
examples).

I tried altering the formula such as
MsgBox Range("D1").Value * 0.01
and a whole lot of other creative variations to represent
the equivalent of
the base formula, but still get the same bad result.
Assigning variables to the numeric components of the
formula did not make a
difference.

Questions:
Am I missing something obvious, or is this a bug anyone
has seen and how did
you work around it?
Is the number 100 a magical or evil number for such
calculations given my
discovery of the numerator being < = 98 and = 175
resulted in success?
What's going on here?

Thanks you guys...any words of wisdom are greatly
appreciated.

Tom Urtis


.