Thanks:  0
Likes:  0

# Thread: Help needed with empty cells.

1. 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.

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

3. 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

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

Thanks again.

5. 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!

6. 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 ]

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

8. 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

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•