Re: Calculation bug or is it me

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

From: Ron Rosenfeld (ronrosenfeld_at_nospam.org)
Date: 09/27/04 

Date: Mon, 27 Sep 2004 13:58:40 -0400

On Mon, 27 Sep 2004 10:00:14 -0700, "Tom Urtis" <tomurtis@comcast.net> wrote:

>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

Quantcast