Results 1 to 14 of 14

20060125, 16:44 #1
 Join Date
 Jan 2001
 Location
 Illinois
 Posts
 552
 Thanks
 0
 Thanked 1 Time in 1 Post
Formula to pull top 5 numbers (Excel 2003)
I have a list of numbers B10:B100. I want to pull the top 5 numbers from this range and put the highest number in B1, 2nd highest in B2, 3rd in B3 etc. up to B5. Is there a formula that does this in excel? Thanks

20060125, 16:59 #2
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Formula to pull top 5 numbers (Excel 2003)
Enter this formula in B1:
=LARGE($B$10:$B$100,ROW())
and fill down to B5. Note: if there are ties, a number may be listed more than once. If you want the 5 highest unique numbers, you'd need different formulas.

20060125, 17:01 #3
 Join Date
 Jun 2001
 Location
 New York, New York, Lebanon
 Posts
 1,449
 Thanks
 1
 Thanked 1 Time in 1 Post
Re: Formula to pull top 5 numbers (Excel 2003)
<img src=/S/hello.gif border=0 alt=hello width=25 height=29> jha900
Can you not simply sort the range B10:B100?
Try and see if the Max function would help in a combination with another function so that you can exclude the higher numbers as you find them.
But I still think you should simply sort the range if you can.
Wassim<img src=/S/compute.gif border=0 alt=compute width=40 height=20> in the <img src=/S/bagged.gif border=0 alt=bagged width=22 height=22>

20060125, 17:08 #4
 Join Date
 Jan 2001
 Location
 Illinois
 Posts
 552
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Formula to pull top 5 numbers (Excel 2003)
ok, thank you.
Also, i now realize I have to pull the matching text in COL A that goes with the highest number. So if Red is 99% and falls in B47, then B1 will have 99% in it. How can I get A1 to have the word Red in it?

20060125, 17:11 #5
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Formula to pull top 5 numbers (Excel 2003)
Are the numbers in column B unique?

20060125, 17:16 #6
 Join Date
 Jan 2001
 Location
 Illinois
 Posts
 552
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Formula to pull top 5 numbers (Excel 2003)
No, they are not unique. They are percentages that could be duplicated.

20060125, 17:23 #7
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Formula to pull top 5 numbers (Excel 2003)
See the thread starting at <post:=493,337>post 493,337</post:>, in particular the reply by <!profile=Aladin Akyurek>Aladin Akyurek<!/profile>.

20060125, 18:23 #8
 Join Date
 Jan 2001
 Location
 Illinois
 Posts
 552
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Formula to pull top 5 numbers (Excel 2003)
I've worked out this way of doing it. Is there a way to add formulas (perhaps vlookup) to A1:B5 so that if a number is entered in B6 that represents one of the 5 categories, that the top 5 of that cat/column will be displayed in A1:B5?

20060125, 18:53 #9
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Formula to pull top 5 numbers (Excel 2003)
In A1:
<pre>=INDEX($A$11:$A$39,MATCH(B1,INDEX($B$11:$F$39 ,0,$B$6),0))</pre>
In B1:
<pre>=LARGE((INDEX($B$11:$F$39,0,$B$6)),ROW())</pre>
Copy A1:B1 to A2:B5
[Note this presumes that there are no duplicates in the top 5, if there are, it gets more complicated and used defined function would be needed]
Steve

20060125, 19:19 #10
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: Formula to pull top 5 numbers (Excel 2003)
Using <!profile=Aladin Akyurek>Aladin Akyurek<!/profile> technique in <post:=493,855>post 493,855</post:> (from Hans's suggestion):
In H11:
<pre>=RANK(I11,$I$11:$I$39)+COUNTIF($I$11:I11,I1 1)1</pre>
In I11:
<pre>=INDEX(B11:F11,$B$6)</pre>
Copy H11:I11 to H12:I39
In A1:
<pre>=INDEX($A$11:$A$39,MATCH(ROW(),$H$11:$H$39,0) )</pre>
In B1:
<pre>=VLOOKUP(ROW(),$H$11:$I$39,2,0)</pre>
Copy A1:B1 to A2:B5
Now it won't matter if there are duplicate percentages.
Steve

20060125, 22:29 #11
 Join Date
 Jan 2001
 Location
 Illinois
 Posts
 552
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Formula to pull top 5 numbers (Excel 2003)
This works perfectly, but there is a twist here. I am using a product called Xcelsius that uses underlying Excel files. I linked this worksheet to Xcelsius and it bombed so I called the vendor and they said they do not support the Row Excel function. Therefore, is there another way to do this without using Row?

20060125, 23:03 #12
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,329
 Thanks
 3
 Thanked 218 Times in 201 Posts
Re: Formula to pull top 5 numbers (Excel 2003)
Replace each instance of the ROW function with the number of the row it is being used in  e.g. in B1, replace ROW() with 1 etc.
HTHRegards,
Rory
Microsoft MVP  Excel

20060126, 18:13 #13
 Join Date
 Jan 2001
 Location
 Illinois
 Posts
 552
 Thanks
 0
 Thanked 1 Time in 1 Post
Re: Formula to pull top 5 numbers (Excel 2003)
ok, thank you very much. I have it all working, see attached. I've given up on the Xcelsius tool, it keeps bombing so now i am trying to design a form in vba.
How do I control the Chart on the UserForm1? I want the user to be able to choose an item from the listbox (choose up to 5) and then have the chart show them the results (like on Chart1) chartsheet. Would this be the best way for the user to see the results of their choice in a chart format? How do I manipulate the chart control on the form? Thank you.

20060126, 20:49 #14
 Join Date
 Mar 2002
 Posts
 84,353
 Thanks
 0
 Thanked 30 Times in 30 Posts
Re: Formula to pull top 5 numbers (Excel 2003)
Your workbook contains a nonstandard control, so I can't do anything with it. That need not be a problem for you, but it might be if you want to distribute the workbook to other users.