Rank Tiebreaker

[daleyt]

I've searched older threads but can't find an answer to my question.

I want to fix a tie breaker in my rank formula. I am currently creating a performance based bonus system for my employees. I gather the data, enter it into a spreadsheet and have a score sheet on a second sheet in the excel workbook. It is ranked in ascending order. Whoever gets the most points gets the biggest percentage of the pot. There are 6 employees. My problem occurs that if there is a tie in one of the columns, both employees receive equal points.

For example:

Employee Projects Comp. Points
Mark 12 4
Dave 12 4
Larry 10 2
Ricky 16 6
Carl 6 1
Steve 13 5


The rank function gives both Mark and Dave equal points for completing the same amount of projects. Rather than giving them 4 points each, is it possible to split the difference and give each 3.5?

 

[VBA Geek]

<b>Worksheet Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #BBB"><thead><tr style=" background-color: #DAE7F5;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #DAE7F5;color: #161120">D2</th><td style="text-align:left">=RANK.AVG(<font color="Blue">B2,$B$2:$B$7,1</font>)</td></tr></tbody></table></td></tr></table><br />

Excel 2013
	A	B	C	D
1	Employee	Projects	Comp.	Points
2	Mark	12	4	3.5
3	Dave	12	4	3.5
4	Larry	10	2	2
5	Ricky	16	6	6
6	Carl	6	1	1
7	Steve	13	5	5
Sheet1

 

[Snakehips]

Solution needs amending will re-post

 

[Snakehips]

daleyt,

Nowhere near as tidy as Geek's solution but a possibility for older versions of Excel......

Excel 2007
	A	B	C
1	Employee	Projects	Points
2	Mark	12	3.5
3	Dave	12	3.5
4	Larry	10	2
5	Ricky	16	6
6	Carl	6	1
7	Steve	13	5
Sheet7

Cell Formulas
Range	Formula
C2	=IF(COUNTIF($B$2:$B$7,B2)=1,RANK(B2,$B$2:$B$7,1),((RANK(B2,$B$2:$B$7,1)*COUNTIF($B$2:$B$7,B2))+ COUNTIF($B$2:$B$7,B2)*(COUNTIF($B$2:$B$7,B2)-1)/2)/COUNTIF($B$2:$B$7,B2))

 

[Marcelo Branco]

Another possible solution for older versions

Formula in C2 copied down
=1+COUNTIF($B$2:$B$7,"<"&B2)+(COUNTIF($B$2:$B$7,B2)-1)*0.5

M.