Formula in Marksheet

  • Thread starter Thread starter Zainuddin Zakaria
  • Start date Start date
Z

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
 
If you want to check each group then you could do something like :

=IF(COUNTIF(D7:J7,">=" & $D$2) + COUNTIF(L7:P7,">=" & $D$2) +
COUNTIF(R7:T7,">=" & $D$2)>=7, IF(COUNTIF(L7:P7,">=" &
$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.
 
Actually this is it:

=IF(COUNTIF(D7:J7,">=" & $D$2) + COUNTIF(L7:P7,">=" & $D$2) +
COUNTIF(R7:T7,">=" & $D$2)>=7, IF(COUNTIF(D7:J7,">=" &
$D$2)>=4,IF(COUNTIF(L7:P7,">=" & $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.
 
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 :-)
 
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(L7:P7,">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
 
Back
Top