Results 1 to 5 of 5
  1. #1
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post
    I have a set of data...

    Trigger cell Total Rank Rank Males Rank Females
    Concept 1 3 4 1
    Concept 2 2 2 2
    Concept 3 4 1 3
    Concept 4 1 3 4

    In the trigger cell i want to enter 1, 2 or 3 and have the 4 rows below sort from lowest to highest (1 to 4). So if the trigger cell (A1) has a 1 in it i want to return...

    Concept 4 1
    Concept 2 2
    Concept 1 3
    Concept 3 4

    If the trigger cell has a 2 then i want...

    Concept 3 1
    Concept 2 2
    Concept 4 3
    Concept 1 4

    Is this possible to do in formulas? Thank you for the help.

  2. #2
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Perhaps the formula posted by Steve in Post 770792 might help you Excel formula,If and Rank

    Regards,

    Tom Duthie

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts
    If your data starts in cell A1 and cell E1 contains the number:

    =INDEX($A$1:$A$4,MATCH(SMALL(OFFSET($A$1:$A$4,0,E1 ),ROW($A$1:$A$4)),OFFSET($A$1:$A$4,0,E1),0))

    (Array formula, enter using control+shift+enter)
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  4. #4
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Illinois
    Posts
    552
    Thanks
    0
    Thanked 1 Time in 1 Post
    Sorry, I'm not quite sure how to use this formula. I'm uploading a file and put the trigger cell in A1. The formula is in F3. Thank you for the help.
    Attached Files Attached Files

  5. #5
    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
    In G2:
    =INDEX(B22,A1)
    In G3-G6: 1,2,3,4, respectively

    In F3:
    =INDEX($A$3:$A$6,MATCH(G3,INDEX($B$3:$D$6,0,$A$1), 0))

    Copy F3 to F4:F6

    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
  •