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.
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.
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 ...
A forum community dedicated to tech experts and enthusiasts. Come join the discussion about articles, computer security, Mac, Microsoft, Linux, hardware, networking, gaming, reviews, accessories, and more!