Formula in Marksheet

[Zainuddin Zakaria]

Hi all...

I need help on a 'formula' for my students' marksheet.

For them to pass the whole exam, a student MUST PASS 7 SUBJECTS from 3
groups.

The grouping is like these :

A) Must pass ALL 4 subjects in Group A - marks are in cells D7, F7, H7, J7

B) Must pass AT LEAST 2 OUT OF 3 subjects in Group B - marks are in cells
L7, N7, P7

C) Must pass AT LEAST 1 OUT OF 2 subjects in Group C - marks are in cells
R7, T7


Thank you so much

 

[Don Guillett]

something like?
if count(d7,f7,h7,j7)<4,"bad","good")

 

[JosephByrns]

If you want to check each group then you could do something like :

=IF(COUNTIF(D7:J7,">=" & $D$2) + COUNTIF(L77,">=" & $D$2) +
COUNTIF(R7:T7,">=" & $D$2)>=7, IF(COUNTIF(L77,">=" &
$D$2)>=2,IF(COUNTIF(R7:T7,">=" & $D$2)>=1,"Pass","Fail"),"Fail"),"Fail")

Where $D$2 is the Pass mark, there may be a neater way, but this should
work.

 

[JosephByrns]

Actually this is it:

=IF(COUNTIF(D7:J7,">=" & $D$2) + COUNTIF(L77,">=" & $D$2) +
COUNTIF(R7:T7,">=" & $D$2)>=7, IF(COUNTIF(D7:J7,">=" &
$D$2)>=4,IF(COUNTIF(L77,">=" & $D$2)>=2,IF(COUNTIF(R7:T7,">=" &
$D$2)>=1,"Pass","Fail"),"Fail"),"Fail"),"Fail")

the previous one wasn't checking all 4 of group A were passes.

 

[Don Guillett]

You are assuming that there would be NO marks in the undesired cells like
e,g,etc

 

[JosephByrns]

Thats true, but I thought it would get even messier if I tried to exclude
the other cells. I'm leaving that to the poster

 

[Sandy Mann]

If I understand your requirements correctly try:

=IF((D7>50)+(F7>50)+(H7>50)+(J7>50)+MIN(((L7>50)+(N7>50)+(P7>50)),2)+MIN((R7>50)+(T7>50),1)>=7,"Pass","Fail")

If the inbetween cell have text but no numbers then:

=IF(COUNTIF(D7:J7,">50")+MIN(COUNTIF(L77,">50"),2)+MIN(COUNTIF(R7:T7,">50"),1)>=7,"Pass","Fail")

Will also work

--
HTH

Sandy
In Perth, the ancient capital of Scotland

(e-mail address removed)
(e-mail address removed) with @tiscali.co.uk