Ranking Numbers If Above/Below A Number

daw382

New Member
Joined
Aug 25, 2011
Messages
10
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:

2013201420152016
A3650
B-25-46
C-44-28
D5-942
E1174

<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?
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Eric W

MrExcel MVP
Joined
Aug 18, 2015
Messages
10,617
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:

ABCDE
12013201420152016
2A3650
3B-25-46
4C-44-28
5D5-942
6E1174
7
8
9
106
1158
12476
13154
14D5-942
153-20
161-4
17-2
18-4

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

Worksheet Formulas
CellFormula
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

New Member
Joined
Aug 25, 2011
Messages
10
Eric, that worked perfectly. Glad you could decipher what I was trying to accomplish. Thank you for your help.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,521
Messages
5,596,645
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top