MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Trying to find Offset from Large


Posted by Cliff on July 01, 2001 1:14 PM

If I have a list of data:

Player HR RBI AVG
------------------------
Alpha 23 100 .300
Bravo 19 93 .310
Charlie 17 85 .290
Delta 13 67 .276

I can use Large(array,rank) to find the largest, second
largest, etc., values in the numerical data columns,
but how can I return the Name that corresponds to
Large(AVG,1)? In other words, it should find .310 in AVG
and return Bravo. I've been trying to use Large in
conjunction with Offset, but I haven't had success. I
realize that there are other ways to do this, but my data
will be changing daily, and I don't want to sort it.

Can this be done?

Thanks,
Cliff


Posted by Aladin Akyurek on July 01, 2001 1:29 PM

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

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

Posted by Cliff on July 02, 2001 3:09 PM

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

Posted by Aladin Akyurek on July 02, 2001 3:35 PM

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

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

,

Posted by Aladin Akyurek on July 02, 2001 4:25 PM

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

Posted by Cliff on July 02, 2001 5:47 PM

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


Posted by Aladin Akyurek on July 02, 2001 11:40 PM

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.


Posted by Cliff on July 03, 2001 4:58 AM

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

Posted by Cliff on July 03, 2001 7:31 AM

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


Posted by Aladin Akyurek on July 03, 2001 8:46 AM

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

Posted by Cliff on July 03, 2001 3:18 PM

Re: I.e.


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