# Archive of Mr Excel Message Board

Back to Forms in Excel VBA archive index
Back to archive home

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

 Check out our Excel Resources

## Re: return "1" if cell is not equal to "0"

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

## Re: return "1" if cell is not equal to "0"

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

## In case you need negatives

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

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

## Re: In case you need negatives

Posted by Andonny on May 22, 2001 5:31 AM
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

## Re: return "1" if cell is not equal to "0"

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

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

## Make that EN --> AND (NT)

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

## Many ways to skin the cat

Posted by Eric on May 22, 2001 9:22 AM
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.

This archive is from the original message board at www.MrExcel.com.