Ranking Numbers If Above/Below A Number

[daw382]

Hello,

I have a table of data that I'd like some help with creating a formula that ranking within each year, depending on if the number is higher or lower than a certain value. Below is an example of the table:

	2013	2014	2015	2016
A	3	6	5	0
B	-2	5	-4	6
C	-4	4	-2	8
D	5	-9	4	2
E	1	1	7	4

<tbody>
</tbody>

The values associated with D will be my baseline for determining if the other numbers within the same year are higher or lower. Anything higher than 5 in 2013 would go above (in this example, there are none) and anything lower would go below the 5. An example of the output for 2013 would look like this:

2013

5

3

1

-2

-4

<tbody>
</tbody>

I'm fine with getting my baseline D values in, but struggling to create a formula that looks at the table and ranks if higher or lower, and ignores my baseline. Any suggestions?

 

[Eric W]

I think your description is a little vague. It sounds like you just want the columns sorted in descending order, which is easy enough to do. But if you want the lists centered on the value in the D row, you could do something like this:

	A	B	C	D	E
1		2013	2014	2015	2016
2	A	3	6	5	0
3	B	-2	5	-4	6
4	C	-4	4	-2	8
5	D	5	-9	4	2
6	E	1	1	7	4
7
8
9
10			6
11			5		8
12			4	7	6
13			1	5	4
14	D	5	-9	4	2
15		3		-2	0
16		1		-4
17		-2
18		-4

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet5​

Worksheet Formulas Cell Formula B9 =IFERROR(LARGE(B$2:B$6,RANK(VLOOKUP($A$14,$A$1:$E$6,COLUMNS($A9:B9)),B$2:B$6,0)+ROW(B9)-ROW(B$14)),"") <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

Put your "center" value in A14, then put this formula in B9, and copy down and across to E18.

Hope this helps.

 

[daw382]

Eric, that worked perfectly. Glad you could decipher what I was trying to accomplish. Thank you for your help.

 

[Eric W]

Happy to help!