A tournament has 18 participating teams with 25 players in each team. Each team has to play five rounds of the Tournament and not all players play all rounds. Scores earned by each player in each round are shown in individual cells. If a player does not play a round, that cell is left empty.
The task is to sum the highest 18 scoring players for each round. Only the highest 18 players per team count towards the teams score. If few of the players have the same score at position 18 then only one of them should be included in the overall score.
One solution is to sort each round of scores for each team in descending order and sum the highest 18 values. This is obviously a time consuming process.
There could be two others ways to solve this problem
Formula driven solution – This uses a spare column, a lengthy formula and the Data > Table functionality. Since Data > Table is a series of array formula, this solution makes the workbook very sluggish. You may refer to my solution in this workbook.
PowerPivot solution – This solution is far better than the formula driven one in as much as no spare columns, lengthy formulas or Data > Tables have been used. The solution in this workbook adds the scores of the highest 18 scoring players per round (If few of the players have the same score at position 18 then all scores are included in the overall score). To use this PowerPivot solution, you need to be using the PowerPivot add-in for MS Excel. This add-in is only available for Excel 2010 and higher versions.
Perform an iterative sum of Top n values across multiple columns
{ 4 Comments }