Calculation bug or is it me
Tom,
OK, I'll bite: what results are you getting? Examples are always good....
HTH,
Bernie
MS Excel MVP
"Tom Urtis" wrote in message
...
Edit, I should have written the first sentence as:
Using XL2K3 XP, no problems with my computer, trying to divide 100 by a
number, then multiplying that quotient by 100, and getting illogical
results.
"Tom Urtis" wrote in 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
|