How to return top 5 scores?

Q

quailhunter

I have an interesting situation in an Excel spreadsheet and I was
wondering if there's a way to handle this?

I'm looking to return the top 5 scores, and the person's name
associated with the score. The problem is, when I use the INDEX MATCH
function, I always return the same person if there is more than one
individual with exactly the same score.

=INDEX(A1:A5,MATCH(LARGE(B1:B5,1),B1:B5,0))

Johnson 154
Alberts 160
Smith 154
Able 120
Reese 110

If I change the '1' in the LARGE function to 2, then to 3... It returns
Alberts, then Johnson, then Johnson again... So, it's only finding the
first occurrence of Johnson and not returning Smith for '3'...

Is there anyway to code this problem so I'd return Alberts, Johnson,
Smith???
 
B

Biff

Hi!

Try this:

Entered as an array using the key combo of CTRL,SHIFT,ENTER:

=INDEX(A$1:A$5,MATCH(LARGE(B$1:B$5+(ROW(B$1:B$5)/10^10),ROW(A1)),B$1:B$5+(ROW(B$1:B$5)/10^10),0))

Copy down as needed.

Biff

"quailhunter" <[email protected]>
wrote in message
news:[email protected]...
 
B

Biff

P.S. -
Is there anyway to code this problem so I'd return Alberts, Johnson,
Smith???

My formula will return:

Alberts
Smith
Johnson
Able
Reese

If you want to "flip" Smith and Johnson just change this portion of the
formula in both places:

+(ROW(B$1:B$5)/10^10

Change to:

-(ROW(B$1:B$5)/10^10

Biff
 
H

Harlan Grove

...
....
I'm looking to return the top 5 scores, and the person's name
associated with the score. The problem is, when I use the INDEX MATCH
function, I always return the same person if there is more than one
individual with exactly the same score.

=INDEX(A1:A5,MATCH(LARGE(B1:B5,1),B1:B5,0))

Johnson 154
Alberts 160
Smith 154
Able 120
Reese 110

If I change the '1' in the LARGE function to 2, then to 3... It
returns Alberts, then Johnson, then Johnson again... So, it's only
finding the first occurrence of Johnson and not returning Smith for
'3'...

Is there anyway to code this problem so I'd return Alberts, Johnson,
Smith???

Another alternative involving only one cell per result but assuming later
results can rely on earlier results. If your sample data above were in
A1:B5, then try these array formulas.

E1:F1:
=INDEX(A1:B5,MATCH(MAX(B1:B5),B1:B5,0),0)

E2:F2:
=INDEX($A$1:$B$5,MATCH(MAX(IF(COUNTIF(E$1:E1,$A$1:$A$5)
*COUNTIF(F$1:F1,$B$1:$B$5)=0,$B$1:$B$5)),
IF(COUNTIF(E$1:E1,$A$1:$A$5)*COUNTIF(F$1:F1,$B$1:$B$5)=0,
$B$1:$B$5),0),0)

Select E2:F2 and fill down as needed.
 
Q

quailhunter

Biff,
Your code worked like a charm. I used the -(ROW(B$1:B$5)/10^10, and go
exactly what I was looking for... Q. How do I modify so the next colum
over in my report will display the score for each individual?
 
B

Biff

Hi!
How do I modify so the next column
over in my report will display the score for each individual?

Use a different, less complicated lookup formula:

Assume the original table is in A1:B5

The extracted top 5 names are in the range D1:D5

In E1 enter this formula and copy down:

=VLOOKUP(D1,A$1:B$5,2,0)

Biff

"quailhunter" <[email protected]>
wrote in message
 
Q

quailhunter

Biff,
Thanks for your excellent feedback. You're a genius...

Thanks again..
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top