Perform an iterative sum of Top n values across multiple columns

{ 4 Comments }

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.

Leave a Comment Cancel reply

Your email address will not be published.

*

  • I have tried to copy the information into the actual sheet I use however I am getting confused and there is another complication as I have two “competition teams” that I collect scores for. One is called Vauxhall and the other is called Vespa. The sheet can be viewed here http://sdrv.ms/15cOC9c
    Once you see it it will make sense

    • Hi,

      Keep trying. I took a lot of time to solve the initial problem. I do not want to recreate everything for you. Put in some effort, spend a few hours. Thereafter, if there are any errors you see, post the file. I will correct for the problem.

  • I will keep trying but it’s not easy when you don’t understand how the formula works.

    Thank you for your help.