MrExcel.com - Your One Stop for Excel Tips & Solutions

return "1" if cell is not equal to "0"

Posted by Andonny on May 22, 2001 4:24 AM

Hi,
I would like to return a "1" in cell C1 if A1 or B1 is a number not equal to "0".

It should not place a "1" into C1 if A1 and B1 has text or blank cells.

Thank you very much once again
Andonny

Posted by Dave Hawley on May 22, 2001 4:35 AM


Hi Andonny

There are many ways to do this, here is but one!


IF(AND(SUM(A1:A2)>A2,SUM(A1:A2)>A1),1,"")

Dave

OzGrid Business Applications

Posted by Dave Hawley on May 22, 2001 4:39 AM


Here's another way that is easier to read:

=IF(AND(A1>0,A2>0,ISNUMBER(A1),ISNUMBER(A2)),1,"")

Dave
OzGrid Business Applications

Posted by Dave Hawley on May 22, 2001 4:45 AM

In case you need negatives

=IF(AND(A1<>0,A2<>0,ISNUMBER(A1),ISNUMBER(A2)),1,"")

OzGrid Business Applications

Posted by Andonny on May 22, 2001 5:31 AM

Re: In case you need negatives

Hi Dave,
Thank you for your reply. One thing I noticed with the formula is that when I place a "1" into B1 and a blank cell into A1 it does not return a "1" in cell C1. I was hoping it would give me a "1".

This is what I tried:
'=IF(AND(A4<>0,B4<>0,ISNUMBER(A4),ISNUMBER(B4)),1,"")

Thank you very much for your time
andonny

Posted by Aladin Akyurek on May 22, 2001 6:43 AM

What about:

=IF(OR(AND(ISNUMBER(A1),A1>0),EN(ISNUMBER(B1),B1>0)),1,"")

Aladin

Posted by Aladin Akyurek on May 22, 2001 6:44 AM

Make that EN --> AND (NT)

=IF(OR(AND(ISNUMBER(A1),A1>0),EN(ISNUMBER(B1),B1>0)),1,"") Aladin

Posted by Eric on May 22, 2001 9:22 AM

Many ways to skin the cat

Again with many answers to your question, I guess pick the one that works best for you. I thought of the formula in column c like so:
=if(or(a1<>0,b1<>0)=true,1,"")
Has the advantage/disadvantage of detecting either polarity and also if there is text in the either cell.