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.
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 this 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 guy’s caddy makes more than that now.
Secret deals aside, you could use Excel to check for ties, and split the prize money among all tied players. The Excel RANK function calculates each player’s standing, and the COUNTIF function can check for ties. Then, 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 players, 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.
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.
Excel RANK Function videos