Formula in Marksheet

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

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

--
Don Guillett
SalesAid Software
[email protected]
"Zainuddin Zakaria" <[email protected]> wrote in message
news:[email protected]...
> 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.

"Zainuddin Zakaria" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>
>
>

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.


"JosephByrns" <[email protected]> wrote in message
news:%[email protected]...
> 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.
>
> "Zainuddin Zakaria" <[email protected]> wrote in message
> news:[email protected]...
>> 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
>>
>>
>>
>>
>>
>
>

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

--
Don Guillett
SalesAid Software
[email protected]
"JosephByrns" <[email protected]> wrote in message
news:uqYR%[email protected]...
> 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.
>
>
> "JosephByrns" <[email protected]> wrote in message
> news:%[email protected]...
>> 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.
>>
>> "Zainuddin Zakaria" <[email protected]> wrote in message
>> news:[email protected]...
>>> 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
>>>
>>>
>>>
>>>
>>>
>>
>>
>
>

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 :-)


"Don Guillett" <[email protected]> wrote in message
news:%[email protected]...
> You are assuming that there would be NO marks in the undesired cells like
> e,g,etc
>
> --
> Don Guillett
> SalesAid Software
> [email protected]
> "JosephByrns" <[email protected]> wrote in message
> news:uqYR%[email protected]...
>> 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.
>>
>>
>> "JosephByrns" <[email protected]> wrote in message
>> news:%[email protected]...
>>> 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.
>>>
>>> "Zainuddin Zakaria" <[email protected]> wrote in message
>>> news:[email protected]...
>>>> 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 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

[email protected]
[email protected] with @tiscali.co.uk


"Zainuddin Zakaria" <[email protected]> wrote in message
news:[email protected]...
> 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
>
>
>
>
>