Rank With Tie Situation

[VinceF]

Greetings,
10 teams with the low score winning 1st place, I'm able to rank them 1 thru 10 with #1 being the winner.
Situation: When two teams tie with the low score (#1) I'd like to designate them as .5
Also if 3 teams tie with the low score (#1) I'd like to designate them as .33, is this possible?
Appreciate any suggestions

VinceF
Win10, Office 2016

 

[dalvin647]

I think this should work for you Vince. Scores in Column A, ranks in Column B.

=RANK(A2,$A$2:$A$11,-1)+IF(COUNTIF($A$2:$A$11,A2)>1,1/COUNTIF($A$2:$A$11,A2),0)

Book1
	A	B
1	Score	Rank
2	95	10.00
3	90	9.00
4	85	6.33
5	85	6.33
6	85	6.33
7	70	5.00
8	60	3.50
9	60	3.50
10	55	2.00
11	50	1.00
Sheet1

Cell Formulas
Range	Formula
B2:B11	B2	=RANK(A2,$A$2:$A$11,-1)+IF(COUNTIF($A$2:$A$11,A2)>1,1/COUNTIF($A$2:$A$11,A2),0)

 

[VinceF]

Dalvin,

Thank you for your reply. However when I use the formula this is what I get. In this example I was hoping to have the low tie scores report as .5 or in the case of 3 low tie scores report as .33
Your thoughts...

VinceF

TWO PLUS & PLUS.xlsm
	Y	Z	AA
2	TEAM	SCORE	RANK
3
4	TEAM 1	+21	5
5	TEAM 2	+13	3
6	TEAM 3	+21	3
7	TEAM 4	+52	4
8	TEAM 5	+38	3
9	TEAM 6	+5	2
10	TEAM 7	+5	1
11	TEAM 8		#VALUE!
12	TEAM 9		#VALUE!
13	TEAM 10		#VALUE!
TWO PLUS

Cell Formulas
Range	Formula
Y4	Y4	=INDEX($B$5:$B$72,MATCH($B9,$B$5:$B$72,0))
Z4	Z4	=IF(ISERROR($D9),"",$D9)
AA4:AA13	AA4	=RANK(Z4,Z4:$Z$13,-1)+IF(COUNTIF(Z4:$Z$13,Z4)>1,1/COUNTIF(Z4:$Z$13,Z4),0)
Y5	Y5	=INDEX($B$5:$B$72,MATCH($B16,$B$5:$B$72,0))
Z5	Z5	=IF(ISERROR($D16),"",$D16)
Y6	Y6	=INDEX($B$5:$B$72,MATCH($B23,$B$5:$B$72,0))
Z6	Z6	=IF(ISERROR($D23),"",$D23)
Y7	Y7	=INDEX($B$5:$B$72,MATCH($B30,$B$5:$B$72,0))
Z7	Z7	=IF(ISERROR($D30),"",$D30)
Y8	Y8	=INDEX($B$5:$B$72,MATCH($B37,$B$5:$B$72,0))
Z8	Z8	=IF(ISERROR($D37),"",$D37)
Y9	Y9	=INDEX($B$5:$B$72,MATCH($B44,$B$5:$B$72,0))
Z9	Z9	=IF(ISERROR($D44),"",$D44)
Y10	Y10	=INDEX($B$5:$B$72,MATCH($B51,$B$5:$B$72,0))
Z10	Z10	=IF(ISERROR($D51),"",$D51)
Y11	Y11	=INDEX($B$5:$B$72,MATCH($B58,$B$5:$B$72,0))
Z11	Z11	=IF(ISERROR($D58),"",$D58)
Y12	Y12	=INDEX($B$5:$B$72,MATCH($B65,$B$5:$B$72,0))
Z12	Z12	=IF(ISERROR($D65),"",$D65)
Y13	Y13	=INDEX($B$5:$B$72,MATCH($B72,$B$5:$B$72,0))
Z13	Z13	=IF(ISERROR($D72),"",$D72)

 

[dalvin647]

Hi Vince:

In your formula, make sure to lock Z4. e.g. change Z4:$Z$13 to $Z$4:$Z$13.

Thanks.

Old:
=RANK(Z4,Z4:$Z$13,-1)+IF(COUNTIF(Z4:$Z$13,Z4)>1,1/COUNTIF(Z4:$Z$13,Z4),0)

New:
=RANK(Z4,$Z$4:$Z$13,-1)+IF(COUNTIF($Z$4:$Z$13,Z4)>1,1/COUNTIF($Z$4:$Z$13,Z4),0)

 

[VinceF]

Thank You Dalvin647, works like a charm.

VinceF