IF statement with AND or OR?
 

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(A20,"Fail","Pass").

Please can you help?
--
Thank you,

Colin.

IF statement with AND or OR?
 

Colin,

Try this:-

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

A1=2
A2=0
Hence unspecified.

Mike

"Colin" wrote:

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(A20,"Fail","Pass").

Please can you help?
--
Thank you,

Colin.

IF statement with AND or OR?
 

Try:

=IF(A10,"Dropped",IF(A20,"Fail","Pass"))

However this will return Pass id both cells are empty

To avoid this use:

=IF(OR(A1="",A2=""),"",IF(A10,"Dropped",IF(A20," 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


Replace @mailinator.com with @tiscali.co.uk


"Colin" wrote in message
...
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(A20,"Fail","Pass").

Please can you help?
--
Thank you,

Colin.

IF statement with AND or OR?
 

Oops,

try this instead

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

Same comment about unspecified bit for a different reason

A1=-1
A2=0


Mike

"Colin" wrote:

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(A20,"Fail","Pass").

Please can you help?
--
Thank you,

Colin.

IF statement with AND or OR?
 

It's been a long day!!

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

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

Mike

"Colin" wrote:

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(A20,"Fail","Pass").

Please can you help?
--
Thank you,

Colin.

IF statement with AND or OR?
 

Hi Colin

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

--
Regards
Roger Govier



"Colin" wrote in message
...
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(A20,"Fail","Pass").

Please can you help?
--
Thank you,

Colin.

IF statement with AND or OR?
 

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


"Colin" wrote:

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(A20,"Fail","Pass").

Please can you help?
--
Thank you,

Colin.

IF statement with AND or OR?
 

Hi everyone,

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

--
Thank you,

Colin.


"Teethless mama" wrote:

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


"Colin" wrote:

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(A20,"Fail","Pass").

Please can you help?
--
Thank you,

Colin.