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

Hi Tomo.

Please supply a list of the rules to apply to each set of results.

"tomo" wrote:


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


--
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 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(A 1: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


"tomo" wrote:


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


--
tomo

Philip


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

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

Thanks

regards

Tomo