Rank with Criteria and 2 tie breaks

[Bazza1979]

HI

I'm looking for some help with a rank issue I'm having.

I need a formula that will rank based on a criteria (Area) and uses 2 tie breaks. So for example if the are 2 scores of that match in Central England then %1 would determine the highest rank, if the score and %1 match the %2 would determine the highest rank. I have a formula that works for the criteria and %1 as a tie break and another that uses 2 tie breaks but I can't seems to figure out how to combine them.

Any help would be great.

I've added a table below that outlines the desired results but if I've not been clear please ask

Rank	Area	Score	%1	%2
1	North West	218	50.00%	50.00%
2	North West	131	33.33%	66.67%
1	Central England	78	0.00%	100.00%
2	Central England	68	100.00%	100.00%
3	Central England	68	100.00%	33.33%
3	North West	58	28.57%	75.00%
4	Central England	50	100.00%	100.00%
5	Central England	46	28.57%	42.86%
6	Central England	45	0.00%	0.00%
4	North West	58	25.00%	50.00%

<tbody>
</tbody>

Thank you

 

[Eric W]

Maybe something like this?

	A	B	C	D	E	F
1	Rank	Area	Score	1%	2%	rank
2	1	North West	218	50.00%	50.00%	1
3	2	North West	131	33.33%	66.67%	2
4	1	Central England	78	0.00%	100.00%	3
5	2	Central England	68	100.00%	100.00%	4
6	3	Central England	68	100.00%	33.33%	5
7	3	North West	58	28.57%	75.00%	6
8	4	Central England	50	100.00%	100.00%	8
9	5	Central England	46	28.57%	42.86%	9
10	6	Central England	45	0.00%	0.00%	10
11	4	North West	58	25.00%	50.00%	7

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

Sheet3​

Array Formulas Cell Formula F2 {=SUM(IF($C$2:$C$11+$D$2:$D$11/100+$E$2:$E$11/10000>=C2+D2/100+E2/10000,1))} <thead> </thead><tbody> </tbody> Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}. Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

 

[Bazza1979]

Hi Eric

Thanks for the response however your suggestion doesn't take into account the area. The rankings I included in my example data are the results I would like to see.

Any ideas?

Thanks

 

[Eric W]

In that case:

=SUM(IF(($C$2:$C$11+$D$2:$D$11/100+$E$2:$E$11/10000>=C2+D2/100+E2/10000)*($B$2:$B$11=B2),1))
with Control+Shift+Enter.

 

[Peter_SSs]

You could also try this standard-entry formula, copied down.

Excel Workbook
	A	B	C	D	E
1	Rank	Area	Score	1%	2%
2	1	North West	218	50.00%	50.00%
3	2	North West	131	33.33%	66.67%
4	1	Central England	78	0.00%	100.00%
5	2	Central England	68	100.00%	100.00%
6	3	Central England	68	100.00%	33.33%
7	3	North West	58	28.57%	75.00%
8	4	Central England	50	100.00%	100.00%
9	5	Central England	46	28.57%	42.86%
10	6	Central England	45	0.00%	0.00%
11	4	North West	58	25.00%	50.00%
Rank