Negative Value not computing
 

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

Negative Value not computing
 

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
.

Negative Value not computing
 

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

.

Negative Value not computing
 

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

.

Negative Value not computing
 

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

.