Formula - pass and fail

[Salza]

Hi all,

I am preparing an exam marksheet.

In one cell, I add in this formula to check if a student pass or fail the
whole exam.
Condition ... the students must not get any "E" to pass the whole exam.

=IF(OR(E7="E",G7="E",I7="E",K7="E",M7="E",O7="E",Q7="E",S7="E",U7="E",W7="E"
,Y7
="E",AA7="E",AC7="E",AE7="E"),"Fail","Pass"))

Now I want to change to another condition.
The students must not get any "E", or "D", or "C" to pass the exam.

Can you help me with the formula.
Thanks.

Regards,
Salza

 

[Frank Kabel]

Hi Salza
if I understood your example correct you only have an entry every
second column starting in column E. Then one way would be
=IF(SUMPRODUCT(((E7:AE7="E")+(E7:AE7="D")+(E7:AE7="C"))*(MOD(COLUMN(E7:
AE7),2)=1))>0,"Fail","Pass")
or (though i'm not fully sure of the seperator ';' due to the
trasnlation of my non-english Excel version)
=IF(SUMPRODUCT((E7:AE7={"E";"D";"C"})*(MOD(COLUMN(E7:AE7),2)=1))>0,"Fai
l","Pass")


HTH
Frank

 

[Roger Govier]

Hi Salza

One way would be
=IF(SUMPRODUCT(--(E7:AE7={"E";"D";"C"})*(MOD(COLUMN(E7:AE7),2)=1))>0,"Fail",
"Pass")

 

[Bob Phillips]

Hi Salza,

=IF(SUM(COUNTIF(E7:AE7,{"E","D","C"}))>0,"Fail","Pass")

--

HTH

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