Nested Formula
 

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)

--
LTR

Nested Formula
 

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


"MichaelS" wrote:

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)

--
LTR

Nested Formula
 

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_ra nge,0))

Copy down 5 cells.

Biff

"MichaelS" wrote in message
...
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
--
LTR


"Ron Coderre" wrote:

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


"MichaelS" wrote:

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)

--
LTR

Nested Formula
 

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.
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Nested Formula
 

Typo correction:

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

2nd line above should read:
data from row2 down

(headers are assumed in row1)
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

Nested Formula
 

Hi back...

I've used RANK many times before, but never like this. This is very good.
It will help make many of my solutions much slimmer and more efficient.
Thanks
Using Rank and then INDEX is good too, but I'm still curious about my
original question. Is it possible to nest LARGE and OFFSET to work as a
lookup ?
Thanks to everyone....
--
LTR


"Biff" wrote:

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_ra nge,0))

Copy down 5 cells.

Biff

"MichaelS" wrote in message
...
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
--
LTR


"Ron Coderre" wrote:

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


"MichaelS" wrote:

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)

--
LTR

Nested Formula
 

Hi!

Is it possible to nest LARGE and OFFSET to work as a
lookup ?

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

Biff

"MichaelS" wrote in message
...
Hi back...

I've used RANK many times before, but never like this. This is very good.
It will help make many of my solutions much slimmer and more efficient.
Thanks
Using Rank and then INDEX is good too, but I'm still curious about my
original question. Is it possible to nest LARGE and OFFSET to work as a
lookup ?
Thanks to everyone....
--
LTR


"Biff" wrote:

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_ra nge,0))

Copy down 5 cells.

Biff

"MichaelS" wrote in message
...
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
--
LTR


"Ron Coderre" wrote:

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


"MichaelS" wrote:

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)

--
LTR