Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 -- |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 -- |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel nested IF formula question | Excel Discussion (Misc queries) | |||
Hide formula | Excel Worksheet Functions | |||
adding row to forumla | Excel Discussion (Misc queries) | |||
UPDATED - Referencing named Ranges within a Nested IF formula | Excel Worksheet Functions | |||
Match / Vlookup within an Array formula | Excel Discussion (Misc queries) |