Calculation bug or is it me

[Tom Urtis]

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

 

[Tom Urtis]

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.

 

[Bernie Deitrick]

Tom,

OK, I'll bite: what results are you getting? Examples are always good....

HTH,
Bernie
MS Excel MVP

 

[Mister T]

5.17428571428571E-02 is the scientific notation for .0517428571428571, right?

 

[Tom Urtis]

Hi Bernie - - did you not see my original message? On the lengthy side with
examples.

 

[Tom Urtis]

Yep...missed that obvious operator, time for more coffee.
Thanks.

 

[Bernie Deitrick]

Tom,

I'm sorry, my newsreader has been acting up....

You're just getting the scientific version of the same number. To control
how a number is displayed, simply use the Format function:

MsgBox Format(Range("A1").Value / Range("B1").Value * Range("C1").Value,
"0.0000000000000")

HTH,
Bernie
MS Excel MVP

 

[Loz]

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

 

[Tom Urtis]

No problem Bernie, thank you, and thanks again to Mister T, my eyes were too
bleary this morning where I interpreted the minus operator as a plus.
Squinting at too much football on weekend television I guess.

 

[Ron Rosenfeld]

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,

I think you are.

0.0571428571428571

and

5.17428571428571E-02

are the SAME value.

The second is scientific notation.


--ron

 

[Tom Urtis]

Yes I was missing the obvious Ron, as Mister T and Bernie and Loz had also
pointed out...thanks for the response.

Tom

 

[Tom Urtis]

Yes they are one and the same, my oversight as others also pointed out on
this Monday where I should start the day over again. Thanks for
responding; have a nice day.

Tom