Grade - One and Four Others

[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.

 

[Arvi Laanemets]

Hi

(You can do it without an additional sheet too, but the formulas will be
very cumbersome then, as you can't use cell ranges in your formula)
Create an additional sheet, p.e. Extra
I assume your original sheet has name Marksheet
On sheet Extra:
A7=IF(OFFSET(Marksheet!$E7,0,(COLUMN(A7)-1)*2)=0,"",OFFSET(Marksheet!$E7,0,(
COLUMN(A7)-1)*2))
and copy the formula to range A7:M7

On sheet Marksheet:
AE7=IF(AND(Extra!A7<>"",(Extra!A7<>"E"),(COUNTIF(Extra!B7:M7,"<>E")-COUNTIF(
Extra!B7:M7,""))>3),"PASS","FAIL")

You can hide the sheet Extra afterwards


Arvi Laanemets

 

[Chip Pearson]

Salza,

Assuming that all the cells have a grade in them, use

=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")+(AC7<>"E"))>=4),"PASS
","FAIL")