Automatic averaging for ties

**WDufault** · 01-10-2011, 08:21 PM

Golfers win money according to what position they finish in tournament. Prize money for each position predetermined. Is there a function, or a combination of functions, that will automatically calculate winnings for each player if there are ties? Example:

1st - $10,000:
2nd - $8000
3rd - $5000
4th - $2000
5th - $1000

Joe, Jim, and Jack finished tied for 2nd, so they split 2nd, 3rd, and 4th money equally. I know they would get $5000 each (average of 2nd,3rd, and 4th money), but is there a a formula I can plug in that will automatically calculate winnings for all ties? Some tournaments have 70 players cashing, and there are many ties for positions. I'm looking to find a way to automatically calculate each player's winnings without having to do the averaging for all the ties.

**MarvinP** · 01-10-2011, 09:08 PM

Hi WDufault and welcome to the forum,

I've done ties by using a scorecard playoff. The USGA uses the 18 hole score first and then the last 9 holes and then the last 6, then 3 then last hole. When I keep scores I also add the last 9 holes divided by 100 plus the last 6 divided by 10000 plus the last etc...

Therefore my scores look like 72.035231304 This means they got a 72 for 18 holes, 35 for the last 9, 23 for the last 6, 13 for the last 3 and a 4 on the last hole. I display only the unit scores so it looks correct but easily do a scorecard playoff by using the above scoring system. We tell everyone this is how it works before the tournament so there is no bias after we see who the people are.

I hope this gives you an idea or two for eliminating ties.
See: http://www.usga.org/handicapping/pub...-Competitions/
for a better explaination.

**daddylonglegs** · 01-10-2011, 09:59 PM

Let's assume you have a table showing the prize money for each position in A2:B6, the first column showing positions 1 to 5 and the second showing the prize money for each of those positions.

Then you have scores in F2:F7 with this formula in G2 copied down to give the rank

=RANK(F2,F$2:F$7,1)

Then you can use this formula in H2 copied down to give the prize money payable

=SUMIFS(B$2:B$6,A$2:A$6,">="&G2,A$2:A$6,"<="&G2+COUNTIF(G$2:G$7,G2)-1)/COUNTIF(G$2:G$7,G2)

I used a RANDBETWEEN formula in F2:F7 to give random scores, press F9 to generate new results, see attached.

Of course you can extend to include more prizes or players. Works on the assumption that the pos numbers are sequential