Negative Value not computing

[Mike]

I have the following code:

Worksheets("Worksheet").Cells(81, 3).Value = -0.01 Then ...

In cell C81 the calculated value is -.01, but when the
code executes it does not evaluate as -.01. But it does
return -.01 when I make it post value to a cell.

Why doesnt it want to evaluate properly in the formula?

TIA
Mike

 

[Andrew Lenczycki]

How is your cell (C81) formatted (i.e. as number, as
currency, decimal places, etc.)?

 

[Mike]

I am only testing if the difference between two cells is 0
or .01 (which are the allowable differences). The
contents of the cells represents dollar amounts, therefore
they will never be greater than 2 decimals. These arent
formatted as currency, just numbers with commas and 2
decimals.

in summary, would my precision value be .01 in this case?

-----Original Message-----
I'm sure this is a rounding issue. Most floating point

numbers can't be represented exactly when

converted from decimal to binary.

Many years ago, after using a statement analogous to

yours that functioned as the trigger to end

a loop, I waited 30 minutes for the computer to finish

the calculation. I finally decided

(correctly) it was in an endless loop.

Then I remembered one of the first things they taught me

re floating point comparisons. That is

to write your statement as

Dim x As Double
x = Worksheets("Worksheet").Cells(81, 3).Value
If Abs(x - (-0.01)) > 1E12 Then ...

Change the 1E12 to whatever precision you required.

 

[Rob Bovey]

Hi Mike,

You can avoid the problem entirely by just explicitly rounding the
contents of the cell you're looking at before comparing it to the constant,
e.g.

If Round(Worksheets("Worksheet").Cells(81, 3).Value, 2) = -0.01 Then

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *

I am only testing if the difference between two cells is 0
or .01 (which are the allowable differences). The
contents of the cells represents dollar amounts, therefore
they will never be greater than 2 decimals. These arent
formatted as currency, just numbers with commas and 2
decimals.

in summary, would my precision value be .01 in this case?

-----Original Message-----
I'm sure this is a rounding issue. Most floating point

numbers can't be represented exactly when

converted from decimal to binary.

Many years ago, after using a statement analogous to

yours that functioned as the trigger to end

a loop, I waited 30 minutes for the computer to finish

the calculation. I finally decided

(correctly) it was in an endless loop.

Then I remembered one of the first things they taught me

re floating point comparisons. That is

 

[Myrna Larson]

I expect you could use > 0.001 in this situation.

Or you can multiply by 100, then convert to an integer

Dim Cents As Long
Cents = Clng(Worksheets("Worksheet").Cells(81, 3).Value * 100)
If Cents <> 0 And Cents <> -1 Then

I am only testing if the difference between two cells is 0
or .01 (which are the allowable differences). The
contents of the cells represents dollar amounts, therefore
they will never be greater than 2 decimals. These arent
formatted as currency, just numbers with commas and 2
decimals.

in summary, would my precision value be .01 in this case?

-----Original Message-----
I'm sure this is a rounding issue. Most floating point

numbers can't be represented exactly when

converted from decimal to binary.

Many years ago, after using a statement analogous to

yours that functioned as the trigger to end

a loop, I waited 30 minutes for the computer to finish

the calculation. I finally decided

(correctly) it was in an endless loop.

Then I remembered one of the first things they taught me

re floating point comparisons. That is