Tech Support Guy banner
Status
Not open for further replies.

Drop lowest score and determine if remaining scores are above 50, return a pass or fail

1K views 6 replies 3 participants last post by  CodeLexicon 
#1 ·
For each student, I have 5 exam marks out of 100 in each subject for a batch of 40 students. I need to identify and drop/ ignore the lowest mark for each set of 5 subject marks for each student. The with the remaining four, I need to check if all four are above 50 marks, then I will return the word pass and if one of the four marks is below 50 then it should return fail. This will be copied down for each student in the class. Thanks in advance.
 
#4 ·
Th


Thank you Bomb #21.
That is quite simple.
But my range is not adjacent like in the screenshot I posted above. When I select the non-concurrent cells, it gives and error #VALUE message. Note that some of the non-contiguous cells I select are blank.

Any suggestions.
 
#5 ·
I don't understand "When I select the non-concurrent cells".

I tried adding COUNTIFs. See from the attached that the formula in S6 is:

=COUNTIF(D6,"<51")+COUNTIF(F6,"<51")

, and returns 1 because 12 in F6 is less than 51. But copied down to S7 to give:

=COUNTIF(D7,"<51")+COUNTIF(F7,"<51")

returns 0, seemingly ignoring the blank in F7.

Any help? If not, try uploading a dummy worksheet to clarify your layout.
 

Attachments

#6 ·
Thank you Bomb #21, that last one worked.

=IF(COUNTIF(C27,"<50")+COUNTIF(E27,"<50")+COUNTIF(G27,"<50")+COUNTIF(I27,"<50")+COUNTIF(K27,"<50")+COUNTIF(M27,"<50")+COUNTIF(O27,"<50")+COUNTIF(Q27,"<50")+COUNTIF(S27,"<50")>1,"FAIL","PASS")

That's how my final formula looks like. So simple, yet we try to think to such complex solutions.
 
#7 · (Edited)
You can just ask if the 4th largest number is less than 50 ...

Code:
=IF(LARGE(C5:S5,4)<50,"Fail","Pass")
Admittedly it does get more complex if there are other numeric values mixed in that range (between the individual subject scores). In that case you'd have to trick the large function into allowing a range of non-contiguous cells ...

Code:
=IF(LARGE(CHOOSE({1,2,3,4,5,6,7,8,9},C6,E6,G6,I6,K6,M6,O6,Q6,S6),4)<51,"Fail","Pass")
 
Status
Not open for further replies.
You have insufficient privileges to reply here.
Top