Calculating League points for multiple tied positions (Excel 2010)

**Sage013** · 07-01-2013, 01:47 PM

Hi everyone,

I'm just trying to set up a league table for a friend to speed up the calculation of league points each week for a league he runs. The problem is how to generate the right amount of points when there are multiple people in tied positions.

Points start at 10 for 1st place and descend to 1 for 10th Place. However, if e.g. 5 people tied for 6th place they would each get a share of the points from position 6th - 10th. So I it will be (5+4+3+2+1)/5= 3 points each.

Below I've laid out an example of a weeks results. I would like to just paste these in to Excel (from an email) and have the points calculated automatically. Is there some formula that can be used or is this heading vba territory?

Many thanks for any help anyone can give. :)

Position	Player Name
1	Player 7
2	Player 5
2	Player 3
4	Player 10
4	Player 8
6	Player 1
6	Player 2
6	Player 4
6	Player 8
6	Player 9

**XOR LX** · 07-01-2013, 02:04 PM

Hi,

Assuming your data is in cells A2:B11, enter this array formula (confirm with CTRL+SHIFT+ENTER, not just ENTER) in C2 and copy down as required:

=AVERAGE(11-(A2+(ROW(INDIRECT("1:"&COUNTIF($A$2:$A$11,A2)))-1)))

Regards