Adjusting prizes to cater for ties

[Max]

Hi guys,

Assume the table below in cols A to C. I'm looking for a formula which can
be placed in the Adj_Prize (col C) to compute the adjusted prizes (pro-rata
basis) depending on the number of ties which may occur within the rankings
in col A. The "fixed" prize money structure is in col B (Prize). Col A
contains a simple rank formula which calcs the ranking based on the scores
in another col, e.g. in A2: =RANK(G2,$G$2:$G$9,1), copied down to A9.

Rank Prize Adj_Prize
1 120,000 120,000
2 100,000 90,000
2 80,000 90,000
4 70,000 60,000
4 60,000 60,000
4 50,000 60,000
7 40,000 40,000
8 30,000 30,000

Thanks

 

[N Harkawat]

on cell c2 type
=SUMIF(A:A,A2,B:B)/COUNTIF(A:A,A2)
and copy it all the way down thru the extent of your range

 

[Max]

Thanks, Norman !
Works great