Ranking and Splitting Values Equally

[ireid3176]

Hi,

Looking for some assistance with ranking.

I have a scenario where i have a pot of money to split based on ranking. I can use the rank function but if the value i am ranking against is duplicated i want to be able rank equal but share the pot accordingly:

Person 1; Score Value = 4; Rank = 4; Reward = £40
Person 1; Score Value = 4; Rank = 3; Reward = £30

I want both to get the same rank, but critically i want the ranking to share the pot. In this case the pot value is £70 so each should get £35. Hopefully that makes sense.

Can anyone help?

Regards

 

[Eric W]

Welcome to the MrExcel board.

Try:

	A	B	C	D	E	F	G	H	I
1	Name	Score	Rank	Prize			Prizes
2	Amy	3	7	0			1	50
3	Beth	6	2	35			2	40
4	Carl	2	8	0			3	30
5	Dan	1	10	0			4	20
6	Emma	2	8	0			5	10
7	Flo	10	1	50
8	Guy	5	4	10
9	Hank	6	2	35
10	Irene	5	4	10
11	Joy	5	4	10

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

Sheet1​

Worksheet Formulas Cell Formula C2 =RANK(B2,$B$2:$B$11) D2 =IFERROR(SUM(OFFSET($H$2,MATCH(C2,$G$2:$G$6,0)-1,0,COUNTIF($C$2:$C$11,C2)))/COUNTIF($C$2:$C$11,C2),0) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>