IF functions

T

tomo

I am trying to work out a fomular for calculating college grades, i have
found the formular for if all grades are the same as in the 1st line
below.

trying to get formular for the examples in 2 to 4 which are causing
problems. any ideas?

A B C D E

1 P P P P = Pass
=IF(AND(A10="p",B10="p",C10="p"),"Pass")

2 P M M M = Pass

3 D M P M = Pass

4 M D D M = Merit

greatful for this formular

tomo
 
P

Philip J Smith

Try

=IF(COUNTIF(A1:D1,"P")>0,"Pass",IF(COUNTIF(A1:D1,"M")>0,"Merit","Distinction"))

See earlier post on rules. This formula assumes

1 Any incidence of a "P" in a set of results gives a maximum grade of a
"pass"
2. If there are no passes but there is a minimum of one "M" in a set of
results then the overall result is a "Merit".
3 If there are no P's or M's then the overall result must be a "D"
(Distinction).

Another initial check might include a test for a blank cell or an invalid
entry.

{=IF(ISNA(MATCH(A1:D1,ValidGrades,0)),"Invalid
Entry",IF(COUNTIF(A1:D1,"P")>0,"Pass",IF(COUNTIF(A1:D1,"M")>0,"Merit","Distinction")))}

This is an array formula so you will need to press [ctrl][shift] and [Enter]
to enter the formula in the cell.

ValidGrades is a named range containing the set {P,M,D}

Regards

Phil
 
T

tomo

Philip


Thanks for the help. starting with your formular and spending 5 min
adapting it i finaly got it working. The formular below is the fina
version which works

=IF(COUNTIF(A14:C14,"p"),"Pass",IF(COUNTIF(A14:C14,"m"),"Merit",IF(COUNTIF(A14:C14,"d"),"Distinction")))

Thanks

regards

Tomo


Try

=IF(COUNTIF(A1:D1,"P")0,"Pass",IF(COUNTIF(A1:D1,"M")0,"Merit","Distinction"))

See earlier post on rules. This formula assumes

1 Any incidence of a "P" in a set of results gives a maximum grade o
a
"pass"
2. If there are no passes but there is a minimum of one "M" in a se
of
results then the overall result is a "Merit".
3 If there are no P's or M's then the overall result must be a "D"
(Distinction).

Another initial check might include a test for a blank cell or a
invalid
entry.

{=IF(ISNA(MATCH(A1:D1,ValidGrades,0)),"Invalid
Entry",IF(COUNTIF(A1:D1,"P")0,"Pass",IF(COUNTIF(A1:D1,"M")0,"Merit","Distinction")))}

This is an array formula so you will need to press [ctrl][shift] an
[Enter]
to enter the formula in the cell.

ValidGrades is a named range containing the set {P,M,D}

Regards

Phil


:


I am trying to work out a fomular for calculating college grades,
have
found the formular for if all grades are the same as in the 1st line
below.

trying to get formular for the examples in 2 to 4 which are causing
problems. any ideas?

A B C D E

1 P P P P = Pass
=IF(AND(A10="p",B10="p",C10="p"),"Pass")

2 P M M M = Pass

3 D M P M = Pass

4 M D D M = Merit

greatful for this formular

tomo
Click to expand...
 
Top