Finding the right Rank

[broncos347]

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
	A	B	C	D	E	F	G	H	I	J	K
3	Station	Building	Rank	Canopy	Rank	Footbridge	Rank	Platform	Rank	Stn Ave	Rank
4	Acle Stn	44.70	67	46.07	63	43.52	41	69.96	13	53.61	37
5	Alresford Stn	21.24	103			51.50	28	50.16	91	50.78	58
6	Althorne Stn			83.33	2			46.08	109	43.80	114
7	Angel Road Stn					55.97	22	60.18	47	46.05	97
PARL Compliance

Cell Formulas
Range	Formula
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
	A	B	C
3		10 Best Stations (out of 132 stns)
4
5	1
6	2
7	3
8	4
9	5
10	6
11	7
12	8
13	9
14	10
Best vs Worst Stns

Cell Formulas
Range	Formula
B3	10 Best Stations (out of 132 stns)
A5	1
A6	2
A7	3
A8	4
A9	5
A10	6
A11	7
A12	8
A13	9
A14	10

 

[Drrellik]

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