Select ranked x cells from y and reselect

Posted by Stan Wilson on June 19, 2001 1:52 AM

Help - I'm a wee bit desperate.
Imagine 90 names of people in Column A with say 20 scores next to each (some scores blank) over the next columns. Without sorting and reprinting rows, how do I select say the best 12 scores from each person and print these somewhere else, and then select the top 5 of these (grandtotals now) and print their names and totals.
I know that I should be able to do it, but I don't have much hair left. Please, all you gurus!!

Stan

Posted by cpod on June 19, 2001 10:04 AM

You can use the Large() function. If the scores for one person are in B2:Z2 then putting this function into cell C2:

=LARGE($B$1:$Z$1,COLUMN()-1)

and copying over the next 20 columns will return the top 20 scores.

Posted by Stan Wilson on June 20, 2001 2:47 PM

Thanks cpod - I don't quite understand the
COLUMN()-1 part.
Stan