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

OzGrid Business Applications


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
OzGrid Business Applications


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,"")

OzGrid Business Applications


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
What about:

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

Aladin


Make that EN --> AND (NT)

Posted by Aladin Akyurek on May 22, 2001 6:44 AM
=IF(OR(AND(ISNUMBER(A1),A1>0),EN(ISNUMBER(B1),B1>0)),1,"") Aladin


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.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.