Excel Ranking Problem

  • Thread starter Thread starter assws
  • Start date Start date
A

assws

Ok here is my issue. I have a excel sheet that has this kind of
layout.

Name Test 1 Test 2 Test 3 Test 4 Average
Curly 99 84 73 68 81.0
John 75 79 88 64 76.5
Keith 65 91 82 42 70.0
Larry 45 91 72 66 68.5
Moe 78 68 65 33 61.0
Pete 80 66 100 55 75.3
Rodger 98 72 84 99 88.3


What I want is to find the top 3 scores for each test and the person
who has that score.

Finding the top 3 scores I have the answer to using the formula
=LARGE(B2:B8,1) Which will return 99.

Now I need to find the person who has the score. What I would Idealy
want as a result is:

Top 3 Top 3 Top 3 Top 3 Top 3
1 99 91 100 99 88.3
2 98 91 88 68 81.0
3 80 84 84 66 76.5

1 Curly Keith Pete Rodger Rodger
2 Rodger Larry John Curly Curly
3 Pete Curly Rodger Larry John

BTW my sheet is 24 cols and 1200+ rows big

Thanks,
 
With Your table in A1:F8

Select B12:B14, type =LARGE(B2:B8,{1;2;3}) and array enter using
CTRL+SHIFT+ENTER
Copy B12:B14, select C12:F12 and paste

Cell B16 put the formula:-

=INDEX($A$2:$A$8,MATCH(B12,B$2:B$8,0))

Now copy and paste to B16:F18
 
Hey Ken thanks for the reply,

I entered the formula and the almost work 100% correctly. If there are
duplicate scores for 1 test (i.e. test 2 with Keith and Larry both
getting a 91) when I enter the formula

=INDEX($A$2:$A$8,MATCH(B12,B$2:B$8,0))

I get Keith twice instead of Keith and then Larry.

Any thoughts?

Thanks,
 
Hi,

My first formula (top left) for the numbers is in I2 (not in B12 as in Ken's
case), so adapt if you need to:

To handle duplicates,
In I8, the following ARRAY (Ctrl-Shift-Enter) formula:
=INDEX($A:$A,LARGE(IF(B$2:B$8=I2,ROW(B$2:B$8)),COUNTIF(I$2:I2,I2)))

Copy to I9:I10, and then to J8:M10

Regards,

Daniel M.
 
Back
Top