software

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=quot;pquot;,B10=quot;pquot;,C10=quot;pquot;),quot;Passquot;)

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.

quot;tomoquot; wrote:

gt;
gt; I am trying to work out a fomular for calculating college grades, i have
gt; found the formular for if all grades are the same as in the 1st line
gt; below.
gt;
gt; trying to get formular for the examples in 2 to 4 which are causing
gt; problems. any ideas?
gt;
gt; A B C D E
gt;
gt; 1 P P P P = Pass
gt; =IF(AND(A10=quot;pquot;,B10=quot;pquot;,C10=quot;pquot;),quot;Passquot;)
gt;
gt; 2 P M M M = Pass
gt;
gt; 3 D M P M = Pass
gt;
gt; 4 M D D M = Merit
gt;
gt; greatful for this formular
gt;
gt; tomo
gt;
gt;
gt; --
gt; tomo
gt;

Try

=IF(COUNTIF(A11,quot;Pquot;)gt;0,quot;Passquot;,IF(COUNTIF(A11,quot; Mquot;)gt;0,quot;Meritquot;,quot;Distinctionquot;))

See earlier post on rules. This formula assumes

1 Any incidence of a quot;Pquot; in a set of results gives a maximum grade of a
quot;passquot;
2. If there are no passes but there is a minimum of one quot;Mquot; in a set of
results then the overall result is a quot;Meritquot;.
3 If there are no P's or M's then the overall result must be a quot;Dquot;
(Distinction).

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

{=IF(ISNA(MATCH(A11,ValidGrades,0)),quot;Invalid
Entryquot;,IF(COUNTIF(A11,quot;Pquot;)gt;0,quot;Passquot;,IF(COUNTIF(A 11,quot;Mquot;)gt;0,quot;Meritquot;,quot;Distinctionquot;)))}

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

Philquot;tomoquot; wrote:

gt;
gt; I am trying to work out a fomular for calculating college grades, i have
gt; found the formular for if all grades are the same as in the 1st line
gt; below.
gt;
gt; trying to get formular for the examples in 2 to 4 which are causing
gt; problems. any ideas?
gt;
gt; A B C D E
gt;
gt; 1 P P P P = Pass
gt; =IF(AND(A10=quot;pquot;,B10=quot;pquot;,C10=quot;pquot;),quot;Passquot;)
gt;
gt; 2 P M M M = Pass
gt;
gt; 3 D M P M = Pass
gt;
gt; 4 M D D M = Merit
gt;
gt; greatful for this formular
gt;
gt; tomo
gt;
gt;
gt; --
gt; tomo
gt;

PhilipThanks 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,quot;pquot;),quot;Passquot;,IF(COUNTIF(A14:C14 ,quot;mquot;),quot;Meritquot;,IF(COUNTIF(A14:C14,quot;dquot;),quot;Distinction quot;)))

Thanks

regards

Tomo

Originally Posted by Philip J SmithTry

=IF(COUNTIF(A1:D1,quot;Pquot;)0,quot;Passquot;,IF(COUNTIF(A1:D1,quot;M quot;)0,quot;Meritquot;,quot;Distinctionquot;))

See earlier post on rules. This formula assumes

1 Any incidence of a quot;Pquot; in a set of results gives a maximum grade of a
quot;passquot;
2. If there are no passes but there is a minimum of one quot;Mquot; in a set of
results then the overall result is a quot;Meritquot;.
3 If there are no P's or M's then the overall result must be a quot;Dquot;
(Distinction).

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

{=IF(ISNA(MATCH(A1:D1,ValidGrades,0)),quot;Invalid
Entryquot;,IF(COUNTIF(A1:D1,quot;Pquot;)0,quot;Passquot;,IF(COUNTIF(A1 :D1,quot;Mquot;)0,quot;Meritquot;,quot;Distinctionquot;)))}

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

Philquot;tomoquot; wrote:

I am trying to work out a fomular for calculating college grades, i havefound the formular for if all grades are the same as in the 1st linebelow.
trying to get formular for the examples in 2 to 4 which are causingproblems. any ideas?
A B C D E
1 P P P P = Pass=IF(AND(A10=quot;pquot;,B10=quot;pquot;,C10=quot;pquot;),quot;Passquot;)
2 P M M M = Pass
3 D M P M = Pass
4 M D D M = Merit
greatful for this formular
tomo

--tomo