Strange Ranking Requirement

[petewally1976]

Hi, I'm new here (fairly basic Excel skills but trying to improve) so go easy on me!

I'm doing a spreadsheet for a gymnastics competition. I have the following data and I want to highlight the top 3:

[TABLE="width: 183"]
<colgroup><col><col span="2"></colgroup><tbody>[TR]
[TD]Name[/TD]
[TD]Bars Score[/TD]
[TD]Bars Ranking[/TD]
[/TR]
[TR]
[TD]Holly[/TD]
[TD="align: right"]8.0[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Emma[/TD]
[TD="align: right"]4.5[/TD]
[TD="align: right"]9[/TD]
[/TR]
[TR]
[TD]Elsie[/TD]
[TD="align: right"]4.9[/TD]
[TD="align: right"]8[/TD]
[/TR]
[TR]
[TD]Jane[/TD]
[TD="align: right"]8.4[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Helen[/TD]
[TD="align: right"]8.3[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Sarah[/TD]
[TD="align: right"]9.9[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Sam[/TD]
[TD="align: right"]9.9[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Hayley[/TD]
[TD="align: right"]8.4[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Imogen[/TD]
[TD="align: right"]3.8[/TD]
[TD="align: right"]10[/TD]
[/TR]
[TR]
[TD]Bethany[/TD]
[TD="align: right"]8.3[/TD]
[TD="align: right"]5[/TD]
[/TR]
</tbody>[/TABLE]


Here I have used the standard ranking and then I would use conditional formatting to highlight the 'bottom 3' in the "Bars Ranking" column (yes I know I could just highlight the top 3 in the "Bars Score" column.

However, what the gym club want is for ties to be handled differently so that in this example, Sarah and Sam are joint first then Jane and Hayley are joint 2nd (NOT 3rd) and Helen and Bethany are joint 3rd (NOT 5th). Holly would then be classed as 4th etc. I know it doesn't make a lot of sense but this is what I've been asked for.

Any idea how I can achieve this?

Many thanks.

 

[FormR]

Hi, welcome to the forum, here is one way you can try, pay special attention to the array formula instructions at the bottom of the example.

Excel 2013/2016
	A	B	C
1	Name	Bars Score	Bars Ranking
2	Holly	8.0	4
3	Emma	4.5	6
4	Elsie	4.9	5
5	Jane	8.4	2
6	Helen	8.3	3
7	Sarah	9.9	1
8	Sam	9.9	1
9	Hayley	8.4	2
10	Imogen	3.8	7
11	Bethany	8.3	3
CALCS 2

Cell Formulas
Range	Formula
C2	{=1+SUM(0+(FREQUENCY(IF($B$2:$B$11>B2,$B$2:$B$11),$B$2:$B$11)>0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[petewally1976]

Thanks FormR that seems to do the trick . Now to work out what it's actually doing (WHY it works)...

 

[FormR]

Great, good to hear

It's effectively getting a unique count of the scores that are higher than itself, it's ranking is then 1+ that count (i.e. 1 lower) so if none are higher it's rank is 0+1.

If you do an internet search for "Excel unique count with condition" you'll probably find quite a few hits that have descriptions of what is going on.