Excel - non vba code - Calculate Prize Money by Place

[Richard2542]

Prizes are award to individual players by Flight; 5 places are paid, in the event of a tie, the prize money is distributed between the players and rounded to the nearest whole dollar amount; i.e., Player 3, 4 and 5 are tied for 2^nd place – the prize award for 2^nd place is $6, 3^rd place is $5 and 4^th place is $3 – the 3 players that are tied for 2^nd place would each receive $4.66 or $5 each (thus eliminating 3^rd and 4^th place winners) – 5^th place would receive a prize.
The player’s place has already been determined by using the RANK Function. And shows:
Player 1 – 1^st place, Player 3 – 2^nd place, Player 4 – 2^nd place, Player 5 – 2^nd place; Player 6 – 5^th place
I need a non vba formula to calculate the winning prize $’s per winning player by Player Flight.

 

[Eric W]

Something like this?

Book1
	A	B	C	D	E	F	G	H
1	Player	Score	Rank	Prize			Prizes	Amount
2	1	100	1	10			1	10
3	2	50	6	0			2	6
4	3	90	2	5			3	5
5	4	90	2	5			4	3
6	5	90	2	5			5	1
7	6	80	5	1
Sheet1

Cell Formulas
Range	Formula
C2	=RANK(B2,$B$2:$B$7)
D2	=IFERROR(ROUND(SUM(OFFSET($H$2,MATCH(C2,$G$2:$G$6,0)-1,0,COUNTIF($C$2:$C$7,C2)))/COUNTIF($C$2:$C$7,C2),0),0)

I'm not sure how Flight works into this. Do you have another column, say E, with a Flight number? Are the rankings by Flight number? Is the prize money the same for each flight?

 

[Richard2542]

Eric...the rankings pertain to each flight - the flights are determined the by the player handicap and the same prize amount is distributed to players within each flight. I do believe that you have captured the essence of my problem with your example. I am very appreciative for this and will test the formula within my workbook over the next week...thanks so much.