Split Prize Money for Tied RANK in Excel

If you’re hosting a golf tournament, with cash prizes for the top ten players, what happens if two or more players are tied in rank?

Maybe if the top two players are tied, you’d have a playoff, but if two players are tied at 3rd, you wouldn’t try to break the tie that way.

Excel RANK Function

In March, I posted an article about Excel’s RANK function, and how you can break ties. In the comments, someone asked how to split the points if players were tied at the same rank.

So, for the two golfers who are tied for 3rd place, you could add the 3rd and 4th place prize money (or points), and divide that amount equally between the tied players.

Split the Prize Money

When I checked Google to see how golf tournament prizes were split, I found an interesting article from Sports Illustrated in September 1962, describing how the players sometimes secretly arranged to split the winnings, before a playoff round.

Those prize amounts were much smaller than today’s prizes! For example, Nicklaus and Palmer tied for first place in the U.S. Open, and split the first place prize of $17,500 and second place $10,500. Probably the last place player’s caddy makes more than that now.

Use Excel Functions

Secret deals aside, you could use Excel to check for ties, and split the prize money among all tied players.

  1. The Excel RANK function calculates each player’s standing
  2. The COUNTIF function can check for ties.
  3. The AVERAGE function would allocate the prize money for each rank.

The Prize Splitting Formula

In the example shown below, student test scores are shown, with the RANK formula in column D.

To split the prize amount among tied students, the Prize formula uses the AVERAGE function, with the OFFSET function finding the range of cells to average.

This formula is entered in cell D2 and copied down to cell D11.

=AVERAGE(OFFSET($K$1,C2,0, COUNTIF($C$2:$C$11,C2)))

Prize formula with AVERAGE, OFFSET and COUNTIF
Prize formula with AVERAGE, OFFSET and COUNTIF

Detailed Instructions and Sample File

You can see the detailed instructions and an explanation of the formula on the Contextures website page, Excel RANK Function Examples, and download the sample file there.

Watch the Excel Prize Splitting Video

To see the steps for splitting prize amounts with an Excel formula and the RANK function, watch this short Excel tutorial video.

___________

2 thoughts on “Split Prize Money for Tied RANK in Excel”

  1. Help, I’ve talked myself into trouble at work again!

    I have a pivot table of work backlog, showing the days outstanding for all jobs of Type in Region. From this I created another pivot table that uses AutoShow to list the five longest outstanding jobs of each type in each region. Management liked it so much they want a time series to show how they’re managing down the mean age of the worst five, and so I thought I’d run this each day and copy paste the mean ages into a growing series elsewhere.

    But when I used Hide Detail to hide the individual jobs, the average age displayed stopped showing the mean of the top five, as it had before, and started showing the mean of the *total* backlog, even though the AutoShow condition is still active!

    This is not the behavior I expected. How can I get what I want in pivot tables, or with functions that don’t require giant formulae to account for the many different subcategories such as type, region, etc.? Help!

  2. The split prize formula works great. The only issue I have is if you have 20 people and only payout for 10 placings and you have 3 people tied for 10th. How would the formula work then?

Leave a Reply

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.