Finding the right Rank

broncos347

Active Member
Joined
Feb 16, 2005
Messages
283
Office Version
  1. 365
Platform
  1. Windows
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
7Angel Road Stn    55.972260.184746.0597
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

What is =ROMAN(40) in Excel?
The Roman numeral for 40 is XL. Bill "MrExcel" Jelen's 40th book was called MrExcel XL.

Drrellik

Well-known Member
Joined
Apr 29, 2013
Messages
773
Office Version
  1. 365
  2. 2016
  3. 2013
  4. 2011
  5. 2010
Platform
  1. Windows
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
 

Forum statistics

Threads
1,175,545
Messages
5,898,051
Members
434,690
Latest member
Shamsuddin M

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