Trying to find Offset from Large

INDEX + MATCH

Cliff,

=INDEX(A2:A5,MATCH(LARGE(D2:D5,1),D2:D5,0))

where A2:A5 contains Players, D2:D5 your AVG's.

Aladin

===============

Re: INDEX + MATCH

Aladin, this works up to a point, but there is a
problem with duplicate data. For example, if Alpha(AVG)
and Bravo(Avg) both =.300, then
=INDEX(A2:A5,MATCH(LARGE(D2:D5,1),D2:D5,0))
returns Alpha
=INDEX(A2:A5,MATCH(LARGE(D2:D5,2),D2:D5,0))
returns Alpha
=INDEX(A2:A5,MATCH(LARGE(D2:D5,3),D2:D5,0))
returns Charlie.

Thanks,
Cliff

Ranking?

Cliff,

I should have known that there would be duplicate average points. But then it seems you want to rank your players from highest to lowest? Right?

Aladin

==============

,

Re: Ranking?

Cliff,

Lets start over. Here is your sample data, a bit modified,

{"Player","HR","RBI","AVG";"Alpha",23,100,0.3;"Bravo",19,93,0.3;"Charlie",17,85,0.4;"Delta",13,67,0.276}

which occupy A1:D5.

In E2 enter: =RANK(D2,D$2:D$5)+COUNTIF(D$2:D2,D2)-1

Copy down as far as needed.

In G2 enter: =IF(ROW()-1<=COUNT($D$2:$D$5),INDEX(A$2:A$5,MATCH(ROW()-1,$E$2:$E$5,0)),"")

Copy down as far as needed.

The worksheet would now look like this:

{"Player","HR","RBI","AVG",0,0,0;"Alpha",23,100,0.3,2,0,"Charlie";"Bravo",19,93,0.3,3,0,"Alpha";"Charlie",17,85,0.4,1,0,"Bravo";"Delta",13,67,0.276,4,0,"Delta"}

Note 1. You can put the COUNT part in the last formula in a cell of its own and the ref of this cell in the formula. Or, if you want to make a list of say 5 players with the highest averages, use this number (d.i. 5) instead of the COUNT part in the last formula.

Note 2. The last formula can be put on a different worksheet. You need then prefix the ranges with the name of the sheet where they are.

I believe this is what you really need.

Aladin

Re: Ranking?

Aladin,

This is getting to what I want, especially lists
of the top-5, top-10, whatever.

However, I got lost in your "Note 1.", where you
say use "use this number (d.i. 5) instead of the
COUNT part in the last formula." What is (d.i. 5)?

I'm tracking five different minor league teams in
a club's farm system, and I'd like to be able to
produce lists of the top-10 players in home runs,
etc., among all those clubs instead of doing
multiple filters or sorts.

Thanks,
Cliff

Re: Ranking?

NOTE 1 WAS ABOUT WHETHER TO LIST EVERYBODY OR TOP N. I PICKED OUT 5 AS EXAMPLE. BTW, "D.I." IS LATIN FOR "IT IS."

THAT WOULDN'T BE TOO DIFFICULT. IF YOU NEED HELP, PROVIDE PERTINENT DATA AND THE QUESTIONS THAT YOU NEED ANSWERS FOR.

Re: Ranking?

My apologies, but I am accustomed used to using i.e. for id est (that is). If I had to guess, I'd say that d.i.
is the German translation, das ist.

I'll extract a subset of my data and zip up the Excel 97 file, then post it on a website. You should understand that I'm
not grasping the logic behind the answers I see to many of the question on this site about using Index, Match, etc. Once
this falls into place, I'll be able to strike out on my own.

Thank you,
Cliff

Re: Ranking?

Okay, I've put up a small workbook at
http://members.home.net/bb-etc/cliff.zip
that has three sheets: Notes, ActualData, Workdata
if you'd like to take a look.

Thank you,
Cliff

I.e.

You're right about i.e. Mea culpa. The one that I used is Dutch. I don't understand how this is slipped into my English, even when you questioned it. I guess it is one of those "cognitive accidents" I'll remember for a long time.

Aladin

Re: I.e.

That's okay. I can't tell from your writing that English isn't your tongue.
Cliff