MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Choosing x cells from y cells

Posted by Stan Wilson on June 14, 2001 4:01 AM

I'm sort of desperate: I have 90 marksmen with 15 target scores each(Names in A, scores in Columns B to P with the occasional absence). How do I select the best 12 scores in a row and print them along with names?
Further, how do I select the best 5 marksmen from the lot?
I realise that I should know this but have reached the mental block stage.
I would be grateful for any help that points in the right direction.

Posted by Joe Was on June 14, 2001 6:29 AM

Use the LARGE function; =LARGE(Range, place from top). Range can also be an Array.
If your scores are in B1:P1 and you put this formula in cells Q1:AB1 (=LARGE(B1:P1,x)
where x = 1 to 12 you will get the top 12 scores with the highest in Q1 and lowest 12th in AB1. Then in cell AC1 SUM the 12 scores, then sort the Names by AC1 the SUM this will give you the finish order of all 90 marksmen. JSW

Posted by Stan Wilson on June 21, 2001 8:02 PM

Thanks Joe for an excellent explanation. Much appreciated.