IF statement with AND or OR?

[Colin]

Hi,

I have a range of cells a1:a3.
In a1 I will have a number between 0 - 5.
In a2 I will have a number between 0 - 5.
In a3 I want a function that returns "Fail" if a2 is above 0, and "Dropped"
if a1 is above 0 irrelevant to what number is in a2. ie: a number above 0 in
a1 will always return "Dropped" no matter what number is a2.
If a1 and a2 are both at 0 then "Pass" will be returned.

My starting point is =IF(A2>0,"Fail","Pass").

Please can you help?

 

[Mike H]

Colin,

Try this:-

=IF(A1+A2=0,"Pass",IF(A1=0,"Dropped",IF(A2>0,"Fail","Unspecified")))

A1=2
A2=0
Hence unspecified.

Mike

 

[Sandy Mann]

Try:

=IF(A1>0,"Dropped",IF(A2>0,"Fail","Pass"))

However this will return Pass id both cells are empty

To avoid this use:

=IF(OR(A1="",A2=""),"",IF(A1>0,"Dropped",IF(A2>0,"Fail","Pass")))

This still assumes that no cell will hold a negative value.


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Mike H]

Oops,

try this instead

=IF(A1+A2=0,"Pass",IF(A1>0,"Dropped",IF(A2>0,"Fail","Unspecified")))

Same comment about unspecified bit for a different reason

A1=-1
A2=0


Mike

 

[Mike H]

It's been a long day!!

My final effort because I overlooked that -1+1=0

=IF(AND(A1=0,A2=0),"Pass",IF(A1>0,"Dropped",IF(A2>0,"Fail","Unspecified")))

Mike

 

[Roger Govier]

Hi Colin

=IF(COUNT(A1,A2)<2,"",IF(A1>0,"Dropped",
IF(A2>0,"Fail",IF(A1+A2=0,"Pass","Other"))))

 

[Teethless mama]

=IF(AND(A1=0,A2=0),"Pass",IF(A1>0,"Dropped","Fail"))

 

[Colin]

Hi everyone,

So many ways to accomplish the same result!
Many thanks to all of you for your swift responses.