One Pass And Plus Five More

[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 FIVE
other subject in column G7 to AC7.

I tried a few formula but don't seem to work. Some formula I got from dear
friends in this newsgroup required all column to be filled.

This is not necessary coz' not all students take all subject.



Please help me with a correct formula.

Thanks.
I appreciate your time.



Regardas,

Salza

 

[Andy Brown]

Salza,

If I read this right, all input cells are A-E or blank. Brute force
method --

=IF(AND(E7<>"E",COUNTIF(E7:AC7,"<>E")-COUNTBLANK(E7:AC7)>4)=TRUE,"PASS","FAI
L")

HTH,
Andy

 

[Bob Phillips]

And a version that doesn't assume the intermediate cells are blank

=IF(AND(E7<>"E",SUMPRODUCT((G7:AC7<>"E")*(MOD(COLUMN(G7:AC7),2)=1))>4),"PASS
","FAIL")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)