Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
5 highest then list names
I've tried several different ways to solve this problem:
I would like to list the names with the 5 highest scores some are tied for position. all the formulae I've tried does not result in what I expected Where am I going wrong?? col A col B 1 John 1 Mary 2 Sue 3 Bob 3 Jester 5 Sam 15 Tina 10 Rex 10 Lowell 4 Grinch =VLOOKUP(LARGE($A$12:$A$100,ROW(1:1)),$A$12:$B$100 ,1,FALSE) =VLOOKUP(LARGE($A$12:$A$100,ROW(5:5)),$A$12:$B$100 ,2,FALSE) 15 Tina 10 Rex 10 Rex 5 Sam 4 Grinch 3 Bob 3 Bob 2 Sue 1 John 1 John gives me duplicates of same number in col A ( will not find next number down - eliminates Mary, Jester and Lowell) =VLOOKUP(MAX(A:A),A:B,2,FALSE)&" "&MAX(A:A) gives me only the MAX with the name I have even used CONCATENATE to solve it, but that did not work either what to do??? Thanks for any help you can offer. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
5 highest then list names
Assuming that A2:B11 contains your data, try the following...
C2, copied down: =RANK(A2,$A$2:$A$11)+COUNTIF($A$2:A2,A2)-1 D1: enter 5, indicating that you want a Top 5 list *Change this number according to the desired Top N list E1: =MAX(IF(A2:A11=INDEX(A2:A11,MATCH(D1,C2:C11,0)),C2 :C11))-D1 ....confirmed with CONTROL+SHIFT+ENTER, not just ENTER F2, copied down and across: =IF(ROWS(F$2:F2)<=$D$1+$E$1,INDEX(A$2:A$11,MATCH(R OWS(F$2:F2),$C$2:$C$11, 0)),"") Hope this helps! In article , ufo_pilot wrote: I've tried several different ways to solve this problem: I would like to list the names with the 5 highest scores some are tied for position. all the formulae I've tried does not result in what I expected Where am I going wrong?? col A col B 1 John 1 Mary 2 Sue 3 Bob 3 Jester 5 Sam 15 Tina 10 Rex 10 Lowell 4 Grinch =VLOOKUP(LARGE($A$12:$A$100,ROW(1:1)),$A$12:$B$100 ,1,FALSE) =VLOOKUP(LARGE($A$12:$A$100,ROW(5:5)),$A$12:$B$100 ,2,FALSE) 15 Tina 10 Rex 10 Rex 5 Sam 4 Grinch 3 Bob 3 Bob 2 Sue 1 John 1 John gives me duplicates of same number in col A ( will not find next number down - eliminates Mary, Jester and Lowell) =VLOOKUP(MAX(A:A),A:B,2,FALSE)&" "&MAX(A:A) gives me only the MAX with the name I have even used CONCATENATE to solve it, but that did not work either what to do??? Thanks for any help you can offer. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
5 highest then list names
Try this array formula to get the name
=INDEX($B$1:$B$20,MATCH(LARGE($A$1:$A$20+1/(ROW($A$1:$A$20)*10^10),ROW(A1)), $A$1:$A$20+1/(ROW($A$1:$A$20)*10^10),0)) as an array formula, you need to commit with Ctrl-Shift-Enter -- HTH Bob Phillips (remove nothere from email address if mailing direct) "ufo_pilot" wrote in message ... I've tried several different ways to solve this problem: I would like to list the names with the 5 highest scores some are tied for position. all the formulae I've tried does not result in what I expected Where am I going wrong?? col A col B 1 John 1 Mary 2 Sue 3 Bob 3 Jester 5 Sam 15 Tina 10 Rex 10 Lowell 4 Grinch =VLOOKUP(LARGE($A$12:$A$100,ROW(1:1)),$A$12:$B$100 ,1,FALSE) =VLOOKUP(LARGE($A$12:$A$100,ROW(5:5)),$A$12:$B$100 ,2,FALSE) 15 Tina 10 Rex 10 Rex 5 Sam 4 Grinch 3 Bob 3 Bob 2 Sue 1 John 1 John gives me duplicates of same number in col A ( will not find next number down - eliminates Mary, Jester and Lowell) =VLOOKUP(MAX(A:A),A:B,2,FALSE)&" "&MAX(A:A) gives me only the MAX with the name I have even used CONCATENATE to solve it, but that did not work either what to do??? Thanks for any help you can offer. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
5 highest then list names
On Thu, 12 Jan 2006 04:16:03 -0800, ufo_pilot
wrote: I've tried several different ways to solve this problem: I would like to list the names with the 5 highest scores some are tied for position. all the formulae I've tried does not result in what I expected Where am I going wrong?? col A col B 1 John 1 Mary 2 Sue 3 Bob 3 Jester 5 Sam 15 Tina 10 Rex 10 Lowell 4 Grinch =VLOOKUP(LARGE($A$12:$A$100,ROW(1:1)),$A$12:$B$10 0,1,FALSE) =VLOOKUP(LARGE($A$12:$A$100,ROW(5:5)),$A$12:$B$10 0,2,FALSE) 15 Tina 10 Rex 10 Rex 5 Sam 4 Grinch 3 Bob 3 Bob 2 Sue 1 John 1 John gives me duplicates of same number in col A ( will not find next number down - eliminates Mary, Jester and Lowell) =VLOOKUP(MAX(A:A),A:B,2,FALSE)&" "&MAX(A:A) gives me only the MAX with the name I have even used CONCATENATE to solve it, but that did not work either what to do??? Thanks for any help you can offer. One way to do this is to sort the table by scores, descending. If you want a formulaic version, you can download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr/ Then, with your two column range named "rng", and the column of scores named "scores", enter these formulas in two adjacent cells: E1: =IF(INDEX(VSORT(rng,Scores),ROWS($1:1),1)=LARGE( Scores,5),INDEX(VSORT(rng,Scores),ROWS($1:1),1),"" ) F1: =IF(INDEX(VSORT(rng,Scores),ROWS($1:1),1)=LARGE( Scores,5),INDEX(VSORT(rng,Scores),ROWS($1:1),2),"" ) Then select both cells and copy/drag down no further than the total number of entries in Scores (dragging further will give a #REF! result). --ron |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
5 highest then list names
It 'sorta' worked for me
I dragged it past the Scores before it came up with any names (E1) it gave me the names --- great the one for (F1) did not work, whichever way I tried. But no loss here, the names were in the correct order I added a new column to the right of the names which equaled the column to the left of the names (so I can use VLOOKUP) which I have done and it works great Thank you all I will no less be tinkering around with a copy of the sheert to further investigate. "Ron Rosenfeld" wrote: On Thu, 12 Jan 2006 04:16:03 -0800, ufo_pilot wrote: I've tried several different ways to solve this problem: I would like to list the names with the 5 highest scores some are tied for position. all the formulae I've tried does not result in what I expected Where am I going wrong?? col A col B 1 John 1 Mary 2 Sue 3 Bob 3 Jester 5 Sam 15 Tina 10 Rex 10 Lowell 4 Grinch =VLOOKUP(LARGE($A$12:$A$100,ROW(1:1)),$A$12:$B$10 0,1,FALSE) =VLOOKUP(LARGE($A$12:$A$100,ROW(5:5)),$A$12:$B$10 0,2,FALSE) 15 Tina 10 Rex 10 Rex 5 Sam 4 Grinch 3 Bob 3 Bob 2 Sue 1 John 1 John gives me duplicates of same number in col A ( will not find next number down - eliminates Mary, Jester and Lowell) =VLOOKUP(MAX(A:A),A:B,2,FALSE)&" "&MAX(A:A) gives me only the MAX with the name I have even used CONCATENATE to solve it, but that did not work either what to do??? Thanks for any help you can offer. One way to do this is to sort the table by scores, descending. If you want a formulaic version, you can download and install Longre's free morefunc.xll add-in from http://xcell05.free.fr/ Then, with your two column range named "rng", and the column of scores named "scores", enter these formulas in two adjacent cells: E1: =IF(INDEX(VSORT(rng,Scores),ROWS($1:1),1)=LARGE( Scores,5),INDEX(VSORT(rng,Scores),ROWS($1:1),1),"" ) F1: =IF(INDEX(VSORT(rng,Scores),ROWS($1:1),1)=LARGE( Scores,5),INDEX(VSORT(rng,Scores),ROWS($1:1),2),"" ) Then select both cells and copy/drag down no further than the total number of entries in Scores (dragging further will give a #REF! result). --ron |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
5 highest then list names
On Thu, 12 Jan 2006 07:49:06 -0800, ufo_pilot
wrote: It 'sorta' worked for me I dragged it past the Scores before it came up with any names (E1) it gave me the names --- great the one for (F1) did not work, whichever way I tried. But no loss here, the names were in the correct order I added a new column to the right of the names which equaled the column to the left of the names (so I can use VLOOKUP) which I have done and it works great Thank you all I will no less be tinkering around with a copy of the sheert to further investigate. That doesn't make sense to me. The formula in E1 should be giving you the highest score, not the name. I suspect you have either made a typo in the formula, or not NAME'd the ranges properly; or perhaps you do not have things set up as you posted with the Scores in column A and the Names in Column B. Try these: E1: =IF(INDEX(VSORT($A$2:$B$100,$A$2:$A$100),ROWS($1:1 ),1)=LARGE( $A$2:$A$100,5),INDEX(VSORT($A$2:$B$100,$A$2:$A$100 ),ROWS($1:1),1),"") F1: =IF(INDEX(VSORT($A$2:$B$100,$A$2:$A$100),ROWS($1:1 ),1)=LARGE( $A$2:$A$100,5),INDEX(VSORT($A$2:$B$100,$A$2:$A$100 ),ROWS($1:1),2),"") The only difference between the two formulas is that the last digit (in the second line) in one is a '1', and in the other it is a '2'. This reflects whether it should be picking up the data from the first column (score column) or second column (name column) of your two column data table. --ron |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
i want to only select one name from a list of the same names | Excel Discussion (Misc queries) | |||
Is there a function to give us the highest value in a list of tex | Excel Worksheet Functions | |||
EXCEL - LINKS cannot easily get list of all links & names in book | Excel Worksheet Functions | |||
How do I print a list of worksheet tab names in a workbook | Excel Worksheet Functions | |||
Setting up a random list from long list of names ? | Excel Discussion (Misc queries) |