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

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

-----Original Message-----
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
.

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.


On Mon, 4 Aug 2003 13:02:01 -0700, "Mike"
wrote:

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

.

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 *


"Mike" wrote in message
...
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.


On Mon, 4 Aug 2003 13:02:01 -0700, "Mike"
wrote:

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

.

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



On Mon, 4 Aug 2003 14:38:18 -0700, "Mike" wrote:

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.


On Mon, 4 Aug 2003 13:02:01 -0700, "Mike"
wrote:

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

.