Nested Formula

M

MichaelS

This should be easy, but I can't seem to get it to work.
I have several columns of data relating to Hockey Team stats. One of the
columns contains total points per player. I would like to use the 'LARGE'
function to identify the top five values in the points coulmn, and return the
associated players name via an 'OFFSET' function. I have tried the
following, which looks OK to me, but doesn't seem to work.
=OFFSET(LARGE(M14:M29,1),0,-8)
 
R

Ron Coderre

Try this:
A1: Rank
B1: Score
C1: Name

A2:A6 enter numbers 1 through 5

B2: =LARGE($N$14:$N$29,A2)
Copy that down through B6

C2: =INDEX($M$14:$M$29,MATCH(B2,$N$14:$N$29,0))
Copy that down through C6

Does that help?

***********
Regards,
Ron
 
M

MichaelS

This works. However, when there are multiple players with the same point
totals, it only matches the first one in the array. I have played with a
method to break the tie by using a small factoring variable based on the
players jersey number. This works, but it doesn't seem very elegant.
When I started playing with the LARGE function, I thought is was pretty cool
that it recognized and ranked the values even though there were duplicates.
I guess I just hoped there was a way to do some sort of a lookup based on the
LARGE that would also recognize and handle duplicate values.
Thanks
 
B

Biff

Hi!

For ties:

Use an additional column and rank the the point totals.

This formula will break any ties:

=RANK(A1,A$1:A$20)+COUNTIF(A$1:A1,A1)-1

Copy down as needed.

Then base the lookup formula on the ranks:

=INDEX(player_name_range,MATCH(ROWS($1:1),ranks_range,0))

Copy down 5 cells.

Biff
 
M

Max

Another option to play with ..

Sample construct available at:
http://www.savefile.com/files/5989658
AutoSort_Descending_MichaelS_wks

Assume player names in col A, total points in col M,
data from row1 down

Using 3 empty cols to the right, say cols N to P

Put in N2: =IF(M2="","",M2-ROW()/10^10)
(Leave N1 empty)

Put in O2: =IF(ISERROR(LARGE($N:$N,ROW(A1))),"",
INDEX(A:A,MATCH(LARGE($N:$N,ROW(A1)),$N:$N,0)))

Put in P2: =IF(O2="","",INDEX(M:M,MATCH(O2,A:A,0)))

Select N2:p2, copy down till the last row of data

Cols O and P will auto-return the full descending sort of the players and
points. Just pick -off the top 5 from the list. In the event of ties, or
even multiple ties, the top 5 may comprise more than the top 5 lines. Tied
lines, if any, will appear in the same relative order that they appear in
the source cols A and M.
 
B

Biff

Hi!
Is it possible to nest LARGE and OFFSET to work as a
lookup ?
Click to expand...

Yes, but using the Index method is much easier, more efficient and results
in a shorter, "less-complicated" formula.

Biff
 
Top