How to return top 5 scores?

[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???

 

[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]...

 

[CLR]

Take a look at Data > Filter > Autofilter..........it will return the entire
row of the Top5 scores, including duplicates.......

Vaya con Dios,
Chuck, CABGx3



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

 

[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

 

[Aladin Akyurek]

See also my post in:

http://tinyurl.com/44ywo

 

[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.

 

[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?

 

[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 D15

In E1 enter this formula and copy down:

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

Biff

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

 

[quailhunter]

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

Thanks again..

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

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