Results 1 to 4 of 4
  1. #1
    Lounger
    Join Date
    Nov 2002
    Location
    Toronto, Ontario, Canada
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Isolate Highest Ranks in Table (2k)

    This shouldn

  2. #2
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Isolate Highest Ranks in Table (2k)

    In O2 enter & copy across to Q2:

    =INDEX($B$1:$N$1,MATCH(COLUMN()-COLUMN($O2)+1,$R2:$AD2,0))&" "&TEXT(INDEX($B2:$N2,MATCH(COLUMN()-COLUMN($O2)+1,$R2:$AD2,0)),"0%")

    In R2 enter & copy across to AD2...

    =IF(B2,RANK(B2,$B2:$N2)+COUNTIF($B2:B2,B2)-1,"")

    Select O2:AD2 & copy down as far as needed.
    Microsoft MVP - Excel

  3. #3
    Lounger
    Join Date
    Nov 2002
    Location
    Toronto, Ontario, Canada
    Posts
    28
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Isolate Highest Ranks in Table (2k)

    Thank You Aladin.

    Your formula works perfectly!!!

    Would you possibly have time to briefly outline how the formula works? I have no experience with the Index and Column functions.

    Not to worry if you don't. I will lookup the functions and work it through... hopefully.

    Thanks again!

    Rob.

  4. #4
    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: Isolate Highest Ranks in Table (2k)

    If you put 1 in O1, 2 on P1, and 3 in Q1
    And put this in cell O2:
    =INDEX($B$1:$N$1,MATCH(LARGE($B2:$N2,O$1),$B2:$N2, 0))&" "&TEXT(LARGE($B2:$N2,O$1),"0%")
    And copy this to O2:Q9

    You do NOT need the intermediate columns.
    LARGE function gets the largest value (1 for 1, 2 for 2nd largest 3 for 3rd largest (value added in O1:Q1)
    MATCH finds the column of the largest value,
    Index gets the header that MATCHes this
    Text formats the largest xth to percent

    Steve

Posting Permissions

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