# Thread: Formula to pull top 5 numbers (Excel 2003)

1. ## 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

2. ## 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.

3. ## 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

4. ## 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?

5. ## Re: Formula to pull top 5 numbers (Excel 2003)

Are the numbers in column B unique?

6. ## Re: Formula to pull top 5 numbers (Excel 2003)

No, they are not unique. They are percentages that could be duplicated.

8. ## 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?

9. ## 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

10. ## Re: Formula to pull top 5 numbers (Excel 2003)

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

11. ## 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?

12. ## 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.
HTH

13. ## 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.

14. ## Re: Formula to pull top 5 numbers (Excel 2003)

Your workbook contains a non-standard 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.

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•