Formula to pull top 5 numbers (Excel 2003)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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?

Reply | Quote

Are the numbers in column B unique?

Reply | Quote

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

Reply | Quote

See the thread starting at post 493,337, in particular the reply by Aladin Akyurek.

Reply | Quote

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?

Reply | Quote

In A1:

=INDEX($A$11:$A$39,MATCH(B1,INDEX($B$11:$F$39,0,$B$6),0))

In B1:

=LARGE((INDEX($B$11:$F$39,0,$B$6)),ROW())

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

Reply | Quote

Using Aladin Akyurek technique in post 493,855 (from Hans’s suggestion):

In H11:

=RANK(I11,$I$11:$I$39)+COUNTIF($I$11:I11,I11)-1

In I11:

=INDEX(B11:F11,$B$6)

Copy H11:I11 to H12:I39

In A1:

=INDEX($A$11:$A$39,MATCH(ROW(),$H$11:$H$39,0))

In B1:

=VLOOKUP(ROW(),$H$11:$I$39,2,0)

Copy A1:B1 to A2:B5

Now it won’t matter if there are duplicate percentages.

Steve

Reply | Quote

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?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote