Probably a very simple logical test...

[jab40]

Hi, I wonder if someone could help me write what I imagine is a very simple formula - I just can't work out how to write it!

I have two columns, each with scores in. The scores go 0, 10, 20, 30, etc up to 100.

I want the code to have three possiblities: i) If both scores are less than or equal to 50, output "Fail"; ii) if one is less than or equal to 50, and the other score is greater than 50, output "Re-test"; iii) if both scores are above 50, ouput "Pass".

Any suggestions gratefully received

 

[Phanmore]

Assuming your data is in cell A1 & B1

=IF(AND(A1<=50,B1<=50),"Fail",IF(OR(AND(A1<=50,B1>50),AND(B1<=50,A1>50)),"Re-test",IF(AND(A1>50,B1>50),"Pass")))

Not very eliquent, but works.

 

[Ganjin]

Deleted xD

 

[ScottR]

Slightly condensed

=IF(AND(A1<=50,B1<=50),"Fail",IF(OR(A1<=50,B1<=50),"Retest","Pass"))

 

[jab40]

Thanks all, very much, thats perfect. I was trying to do something similar myself but couldn't work out how to get the AND and OR expressions - how do these work in excel?

Also, is there anyway of getting it to not return any value (or another output like "n/a") if one or both the scores are currently blank?

 

[Ruddles]

=IF(OR(A1="",B1=""),"N/a",CHOOSE((A1>50)+(B1>50)+1,"Fail","Re-test","Pass"))

 

[jab40]

=IF(OR(A1="",B1=""),"N/a",CHOOSE((A1>50)+(B1>50)+1,"Fail","Re-test","Pass"))

Superb, thankyou very much!

Would you mind briefly just talking me through what the formula is doing - that way I can learn how it works for next time!

 

[Ruddles]

[B][COLOR=red]=[/COLOR][COLOR=red]IF(OR(A1="",B1=""),"N/a",[/COLOR][/B]CHOOSE((A1>50)+(B1>50)+1,"Fail","Re-test","Pass")[COLOR=red][B])[/B][/COLOR]

The red bit: IF A1 OR B1 is blank, put "N/a" in this cell, otherwise put CHOOSE((A1>50)+(B1>50)+1,"Fail","Re-test","Pass") in this cell.

The blue bit adds 1 if A1>50 and 1 if B1>50, so you end up with a number between 0 and 2 depending on how many tests the person has passed.

Then you add 1 to that number (the pink bit) to turn it into a number between 1 and 3, and finally CHOOSE selects the first, second or third value from the list.

 

[jab40]

[B][COLOR=red]=[/COLOR][COLOR=red]IF(OR(A1="",B1=""),"N/a",[/COLOR][/B]CHOOSE((A1>50)+(B1>50)+1,"Fail","Re-test","Pass")[COLOR=red][B])[/B][/COLOR]

The blue bit adds 1 if A1>50 and 1 if B1>50, so you end up with a number between 0 and 2 depending on how many tests the person has passed.

I don't understand what bit of this code tells it to add 1?

 

[Ruddles]

Look at (A1>50)+(B1>50)+1. It's adding three things together:-

1. The truth of A1>50 (1 if true, 0 if false)
2. The truth of B1>50 (1 if true, 0 if false)
3. 1

So if the person passes both tests, this evaluates to (1)+(1)+1=3.
If they pass the first and fail the second, it evaluates to (1)+(0)+1=2.
If they fail the first and pass the second, it evaluates to (0)+(1)+1=2.
If they fail both tests, it evaluates to (0)+(0)+1=1.

I then use the value to pick the 1st, 2nd or 3rd item from the list.