Finding the right Rank

broncos347

Active Member
Hi,

i have a table of locations against each location there are four columns which each have a average and each column has been ranked using the following formula =IFERROR(RANK.EQ(B4,\$B\$4:\$B\$135),""). to complicate things not every cell in the columns have an average.

Book1
ABCDEFGHIJK
3StationBuildingRankCanopyRankFootbridgeRankPlatformRankStn AveRank
4Acle Stn44.706746.076343.524169.961353.6137
5Alresford Stn21.24103  51.502850.169150.7858
6Althorne Stn  83.332  46.0810943.80114
PARL Compliance
Cell Formulas
RangeFormula
B4=IF('Monthly Stn Data'!B2>0.01,'Monthly Stn Data'!B2,"")
B5=IF('Monthly Stn Data'!B3>0.01,'Monthly Stn Data'!B3,"")
B6=IF('Monthly Stn Data'!B4>0.01,'Monthly Stn Data'!B4,"")
B7=IF('Monthly Stn Data'!B5>0.01,'Monthly Stn Data'!B5,"")
C4=IFERROR(RANK.EQ(B4,\$B\$4:\$B\$135),"")
C5=IFERROR(RANK.EQ(B5,\$B\$4:\$B\$135),"")
C6=IFERROR(RANK.EQ(B6,\$B\$4:\$B\$135),"")
C7=IFERROR(RANK.EQ(B7,\$B\$4:\$B\$135),"")
D4=IF('Monthly Stn Data'!C2>0.01,'Monthly Stn Data'!C2,"")
D5=IF('Monthly Stn Data'!C3>0.01,'Monthly Stn Data'!C3,"")
D6=IF('Monthly Stn Data'!C4>0.01,'Monthly Stn Data'!C4,"")
D7=IF('Monthly Stn Data'!C5>0.01,'Monthly Stn Data'!C5,"")
E4=IFERROR(RANK.EQ(D4,\$D\$4:\$D\$135),"")
E5=IFERROR(RANK.EQ(D5,\$D\$4:\$D\$135),"")
E6=IFERROR(RANK.EQ(D6,\$D\$4:\$D\$135),"")
E7=IFERROR(RANK.EQ(D7,\$D\$4:\$D\$135),"")
F4=IF('Monthly Stn Data'!D2>0.01,'Monthly Stn Data'!D2,"")
F5=IF('Monthly Stn Data'!D3>0.01,'Monthly Stn Data'!D3,"")
F6=IF('Monthly Stn Data'!D4>0.01,'Monthly Stn Data'!D4,"")
F7=IF('Monthly Stn Data'!D5>0.01,'Monthly Stn Data'!D5,"")
G4=IFERROR(RANK.EQ(F4,\$F\$4:\$F\$135),"")
G5=IFERROR(RANK.EQ(F5,\$F\$4:\$F\$135),"")
G6=IFERROR(RANK.EQ(F6,\$F\$4:\$F\$135),"")
G7=IFERROR(RANK.EQ(F7,\$F\$4:\$F\$135),"")
H4=IF('Monthly Stn Data'!E2>0.01,'Monthly Stn Data'!E2,"")
H5=IF('Monthly Stn Data'!E3>0.01,'Monthly Stn Data'!E3,"")
H6=IF('Monthly Stn Data'!E4>0.01,'Monthly Stn Data'!E4,"")
H7=IF('Monthly Stn Data'!E5>0.01,'Monthly Stn Data'!E5,"")
I4=IFERROR(RANK.EQ(H4,\$H\$4:\$H\$135),"")
I5=IFERROR(RANK.EQ(H5,\$H\$4:\$H\$135),"")
I6=IFERROR(RANK.EQ(H6,\$H\$4:\$H\$135),"")
I7=IFERROR(RANK.EQ(H7,\$H\$4:\$H\$135),"")
J4=AVERAGE(B4:H4)
J5=AVERAGE(B5:H5)
J6=AVERAGE(B6:H6)
J7=AVERAGE(B7:H7)
K4=IFERROR(RANK.EQ(J4,\$J\$4:\$J\$135),"")
K5=IFERROR(RANK.EQ(J5,\$J\$4:\$J\$135),"")
K6=IFERROR(RANK.EQ(J6,\$J\$4:\$J\$135),"")
K7=IFERROR(RANK.EQ(J7,\$J\$4:\$J\$135),"")

i have created a separate sheet where i can show the top/worst 10 scoring locations for each block and overall in a series of tables like the one below. is anyone able to advise on a formula that i could use?

Book1
ABC
310 Best Stations (out of 132 stns)
4
51
62
73
84
95
106
117
128
139
1410
Best vs Worst Stns
Cell Formulas
RangeFormula
B310 Best Stations (out of 132 stns)
A51
A62
A73
A84
A95
A106
A117
A128
A139
A1410

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)

Drrellik

Well-known Member
Hello,

Looking at your table column J is an average of all columns from B:I which averages the rank order in C,E,G & I along with your four column averages in B,D,F,& H and then in K you create a rank order again.

What do you want to consider when you rank the top 10 and the worst 10. and did you mean to average all the Rank orders into J?

~DR

Replies
1
Views
218
Replies
1
Views
269
Replies
2
Views
379
Replies
4
Views
264
Replies
7
Views
156

1,171,030
Messages
5,873,389
Members
432,977
Latest member
Alfonso_01

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.

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

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