how to calculate prize money when tied

[touque]

Hello I'm trying to create a sports pool for the Masters golf tournament starting today. I cannot figure out how to average out the prize money if there are ties. For example if there are 4 golfers tied for 5th and the money list shows that 5th gets $300,000, 6th - $270,000, 7th - $251,000 and 8th - $232,000. How can I get excel to recognize they are tied and then to return the average amount for the 4 positions. In this case they would each get $263,500. another note please talk dumb to me because I am far from a guru.

 

[GlennUK]

This should give you some ideas:

Excel Workbook
	A	B	C	D	E	F	G
1	Position	Prize money		Golfer	Score	Rank	Prize money
2	1	400000		Bob	93	10	10000
3	2	375000		Dave	83	5	263250
4	3	350000		Tim	83	5	263250
5	4	325000		Steve	79	2	362500
6	5	300000		Kev	83	5	263250
7	6	270000		Len	83	5	263250
8	7	251000		Mike	88	9	200000
9	8	232000		Fred	82	4	325000
10	9	200000		Sam	78	1	400000
11	10	10000		Keith	79	2	362500
12	11	0
Sheet2

 

[barry houdini]

Can I suggest one possible improvement, Glenn? You could use AVERAGE rather than repeating the COUNTIF function, i.e. in G2 copied down

=AVERAGE(OFFSET($B$1,MATCH(F2,$A$2:$A$12,0),0,COUNTIF($F$2:$F$11,F2),1))

 

[GlennUK]

yeah, I kind of hurried that, didn't I.

 

[touque]

Thanks guys, I'll give it a try. Glen your sheet is exactly what I am doing. I'll try it out during my lunch break.

 

[touque]

Thanks guys it worked perfectly. Now I have to figure out how it works. Once again thanks, great forum.

 

[GlennUK]

I'm glad to have helped.