Calculated column to calculate the top 3 scores from 7.

Hi all,

I have an issue with calculated columns within a SharePoint list if anyone can help?

Our golf society have 7 outings during the year and they have worked out the order of merit to be the total points won over the year and based everyones position in the league on this. They would like to change this to be the best 3 scores from any game they attend during the year to make it fairer.

I’ve been able to do this in Excel by using the LARGE function but the formula doesn’t transfer to SharePoint 2010.

The list currently has 16 columns – Player name, total points & 2 columns for each outing (score & points).

They would like to have total of the top 3 scores (if they have only played in 2 games the 3rd would be 0 and their total would be based on the 2 games + the 0) from any outing over the year to work out the leader board which will automatically change after every game (the default view would be ordered by points desc).

e.g. column 1 = 20, column 2 = 10, column 3 = 30, column 4 = 5 the total would be 60 in my calculated field as column 4 doesn’t come into the top 3.

After the next outing column 5 = 35 so in my calculated column the 10 from column 2 would be dropped and my new total would be 85 for the list item (player name)

So we are keeping a running total of the players top 3 scores (or either top 2 + 0 or top 1 + two 0’s)

Any help with this would be greatly appreciated 🙂

Cheers,
Dave