Results 1 to 14 of 14
  1. #1
    4 Star Lounger
    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

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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.

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

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

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

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

    Are the numbers in column B unique?

  6. #6
    4 Star Lounger
    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.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 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>.

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

  9. #9
    WS Lounge VIP sdckapr's Avatar
    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

  10. #10
    WS Lounge VIP sdckapr's Avatar
    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

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

  12. #12
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 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.
    HTH
    Regards,
    Rory

    Microsoft MVP - Excel

  13. #13
    4 Star Lounger
    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.

  14. #14
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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
  •