Grading System

[Salza]

Hi all,

I am preparing an exam marksheet.

I have 13 columns for grade .... which are E7, G7, I7, K7, M7, O7, Q7, S7,
U7, W7, Y7, AA7, AC7
The five grades are A,B,C,D and E.
Grade E is a fail.
Column AE7 shows the overall result ... PASS or FAIL.

For a student to pass the whole exam (marked as PASS in Column AE7), he must
pass a subject in E7 (get at least a D) AND he must also pass any FOUR other
subject in column G7 to AC7.

I tried a few formula but don't seem to work.

Please help me with a correct formula.

Thanks.
I appreciate your time.

 

[Ecco]

Hello,

Try this formula:

=IF(AND(E7<>"E";COUNTIF(G7:AC7;"E")<9);"PASS";"FAIL")

This should work, if you don't have data in those cells
F7, H7, J7, etc. (or the cell value in any of those cells
is not letter E).

Ecco

 

[Vasant Nanavati]

Hi:

Awkward, but try:

=IF(AND(E7<>"E",SUM(G7<>"E",I7<>"E",K7<>"E",M7<>"E",O7<>"E",Q7<>"E",S7<>"E",
U7<>"E",W7<>"E",Y7<>"E",AA7<>"E")>=4),"PASS","FAIL")

Regards,

Vasant.

 

[Tom Ogilvy]

=IF(AND(E7<"E",SUMPRODUCT(1*CHOOSE({1,2,3,4,5,6,7,8,9,10,11},G7<"E", I7<"E",
K7<"E", M7<"E", O7<"E", Q7<"E", S7<"E",U7<"E", W7<"E", Y7<"E", AA7<"E",
AC7<"E"))>=4),"PASS","FAIL")

or enter this formula
=IF(AND(E7<"E",SUM(IF(MOD(COLUMN(G7:AC7),2)=1,(G7:AC7<"E")*1))>=4),"PASS","F
AIL")
with ctrl+Shift+Enter rather than just enter since it is an array formula.

In either case, you can then drag fill the formula down the column.

empty cells will pass the less than "E" test, so it assumes that all cells
in the columns will have either A,B,C,D,E in it.