Ranking based on a cell value

Topic

New Reply

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.

Reply | Quote

Replies

Perhaps the formula posted by Steve in Post 770792 might help you [post=”770792″]Excel formula,If and Rank[/post]

Regards,

Tom Duthie

Reply | Quote

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)

Reply | Quote

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.

Reply | Quote

In G2:
=INDEX(B2:D2,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

Reply | Quote