Marksheet

[Salza]

Having a problem to get a correct formula for overall passing.

Marks are keyed into the following cells for all the 26 subjects.

Cell F8 = Subject 1
Cell H8-R8 = Subjects 2-7
Cell T8-BE8 = Subject 8-26

To pass the overall exam - a student must pass FIVE subjects (Subject
1, any TWO from Subjects 2-7, and any TWO from Subjects 8-26).

Passing mark for each subject is 36.

Can someone help me with the formula? Thank you.

 

[Rick Rothstein]

I think this does what you want...

=AND(F8<>"",COUNTA(H8:R8)>1,COUNTA(T8:BE8)>1)

but I do note that your H8:R8 and T8:BE8 ranges span more columns that there
are subject to fill them... note sure how, or if, that affects the formula.

 

[Salza]

I think this does what you want...

=AND(F8<>"",COUNTA(H8:R8)>1,COUNTA(T8:BE8)>1)

but I do note that your H8:R8 and T8:BE8 ranges span more columns that there
are subject to fill them... note sure how, or if, that affects the formula.

Thanks a lot, Rick. That works fine. Great help from you,
For every marks, there is a column for grading too. That's why there
are many columns.

 

[Rick Rothstein]

I repaired the ordering of the responses (so my comments are below), but it
is always a good idea to follow the posting style (top posting or bottom
posting your answers) to match the style of the first responder... that way
people looking the thread up in the future (via the Google archives) will be
able to follow the flow of responses in the order they were posted.

On May 18, 1:08 am, "Rick Rothstein"


Thanks a lot, Rick. That works fine. Great help from you,
For every marks, there is a column for grading too. That's why there
are many columns.

What I wasn't sure of was if "extra" entries in the columns (the grading if
I understand you correctly) won't affect the counts for any particular
range). For example, in Columns H through R, if you had a single mark along
with a grade for it, then my formula would return the wrong result... it
would see the mark and its grade as two separate entries to be counted even
though there is only one subject filled in in the range. That was my concern
and the reason I added the part at the end of my post.

 

[GS]

$0.02
I have a student grades manager app that does similar in that it groups
scores by subject context but tallies grades in a separate area where
each group is assigned a 'weight' in the final grade. Output is
Weight%, Grade%, and PointAvg. Avoiding calc errors in the grouped
scores precludes that grades must not be stored there or they'll be
included in the tallies for the group.

Garry