Help needed with empty cells.

[Amortcal]

I'm using the following to enter values in cell B1 on sheet 2:

=(Sheet1!A1>Sheet1!A2)*100+(Sheet1!A1=Sheet1!A2)*50

The problem is, the formula is responding to empty cells. When both cells are empty, the formula is accepting that the cells are equaled and returning a value of one. This is messing up everything. I want the formula to ignore the cell if the cell is empty. What should I do?

Thanks.

[Mark W.]

=(Sheet1!A1>Sheet1!A2)*100+AND((Sheet1!A1=Sheet1!A2),LEN(Sheet1!A1)>0)*50

[Aladin Akyurek]

On 2002-05-12 14:36, Amortcal wrote:
I'm using the following to enter values in cell B1 on sheet 2:

=(Sheet1!A1>Sheet1!A2)*100+(Sheet1!A1=Sheet1!A2)*50

The problem is, the formula is responding to empty cells. When both cells are empty, the formula is accepting that the cells are equaled and returning a value of one. This is messing up everything. I want the formula to ignore the cell if the cell is empty. What should I do?

Thanks.

=(Sheet1!A1>Sheet1!A2)*100+ISNUMBER(A1)*(Sheet1!A1=Sheet1!A2)*50

would return 0 in case A1 and/or A2 are empty.

Is that what you would want to have?

Aladin

[Amortcal]

Aladin thanks...
You really know your stuff...it works just the way I want it to.

Thanks again.

[Mark W.]

On 2002-05-12 14:55, Amortcal wrote:
Aladin thanks...
You really know your stuff...it works just the way I want it to.

Thanks again.

Just be sure that Sheet1!A1 will never contain a text representation of a numeric value!

[Aladin Akyurek]

it works just the way I want it to.

Are you sure? I actually attempted to change my last reply into a question by editing it. It seems I didn't succeed. So I'm posing that question now:

What result do you want to see
when

A1 or A2 is blank

and when

both A1 and A2 are blank?


[ This Message was edited by: Aladin Akyurek on 2002-05-12 15:15 ]

[Amortcal]

It actually worked just the way I wanted it to.

When A1 is blank I want the result to be zero, and the same when A2 is blank. That is, once a cell is blank the result should be zero.

Thanks

[Aladin Akyurek]

On 2002-05-12 15:29, Amortcal wrote:
It actually worked just the way I wanted it to.

When A1 is blank I want the result to be zero, and the same when A2 is blank. That is, once a cell is blank the result should be zero.

Thanks

May I suggest using

=(COUNT(Sheet1!A1:A2)=2)*((Sheet1!A1>Sheet1!A2)*100+(Sheet1!A1=Sheet1!A2)*50)

instead.

Aladin