Nested If statements

[RoniMurphy]

Can you help please?

I have a table where each student has sat 2 papers. If they get 50 or
over it is a pass, if they get 80 or more they distinction and if they
get less then 50, it is a fail. This works fine if they pass or fail
both papers, however if they fail one but get a distinction in the
other, my formula is not working.

thank you

Roni

 

[Guest]

=IF(A1>=80,"Distinction",IF(A1>50,"Pass","Fail"))

What formula are you using that doesn't work?

Dave

 

[Guest]

Try something like this:

With
A1: Student
A2: (a name)

B1: Paper_1
B2 (a score)
C1: Paper_2
C2: (a score)

This formula averages the 2 scores and looks up the result.
D2: =LOOKUP(AVERAGE(B2:C2),{0;50;80},{"FAIL";"PASS";"DISTINCTION"})

OR...if you put this table in cells F1:G3
0 FAIL
50 PASS
80 DISTINCTION

Then
D2: =LOOKUP(AVERAGE(B2:C2),$F$1:$F$3,$G$1:$G$3)

Either way, you can copy the formula in D2 down as far as you need.

Is that something you can work with?
***********
Regards,
Ron

XL2002, WinXP

 

[Richard Buttrey]

If this is your truth table.....


Paper1 Paper2 Result
<50 <50 Fail
<50 50 to <80 Fail
<50 >=80 Fail
50 to <80 <50 Fail
50 to <80 50 to <80 Pass

=80 <50 Fail
=80 50 to <80 Pass
=80 >=80 Distinction

Then with a Paer 1 score in A1 and Paper2 score in B1, then the
following will work.

=IF(AND(A1>=80,B1>=80),"Distinction",IF(AND(A1>=50,B1>=50),"Pass","Fail"))

HTH

Can you help please?

I have a table where each student has sat 2 papers. If they get 50 or
over it is a pass, if they get 80 or more they distinction and if they
get less then 50, it is a fail. This works fine if they pass or fail
both papers, however if they fail one but get a distinction in the
other, my formula is not working.

thank you

Roni

__
Richard Buttrey
Grappenhall, Cheshire, UK
__________________________