Help with Golf League Points Calculation

[joerenaud]

Hi There,

Trying to use Excel to eliminate a weekly and mistake-prone hand calculation of our golf league event results and a points calculation. We assign points to where people place and any ties split (or average) the points for the tied spots. (e.g. if two people say tie for third, they split the points for third and forth place) I can get RANK to work pretty well for determining 1st, 2nd, 3rd place etc and it seems handles ties the way we need it. I then tried to use a VLOOKUP to get points from a range of two columns, place finished and points earned. That works until it encounters a tie and now I'm stuck. I've googled this and have seen some simliar points assignment issues for people but some of the recommended approaches to accomplishing this are very different like the use of OFFSET and COUNTIFS. But this all goes over my head pretty quickly.

Am I'm headed down the right path with RANK and VLOOKUP and something else after that? How would you accomplish this? I hope I have provided here the correct data for you all to see in the worksheet.

20221212_mrexcel_example.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Flight	Name	Gross	Hdcp	Net	Place Finished	Points Earned		Place	Prize
2	A	Player 1	80	10	70	1	150		1	150
3		Player 2	84	9	75	2	125		2	125
4		Player 3	87	10	77	3	100		3	100
5		Player 4	84	7	77	3	#N/A		4	75
6		Player 5	85	7	78	5	70		5	70
7		Player 6	88	9	79	6	65		6	65
8		Player 7	84	4	80	7	60		7	60
9		Player 8	90	9	81	8	55		8	55
10		Player 9	91	8	83	9	50		9	50
11		Player 10	94	8	86	10	45		10	45
12									11	40
13									12	35
14									13	30
15									14	25
16									15	20
17									16	15
18									17	10
19									18	5
20									19	0
Net Scores

Cell Formulas
Range	Formula
F2:F11	F2	=RANK(E2,$E$2:$E$11,1)
G2:G11	G2	=VLOOKUP(F2,I2:J20,2)

Thank you for your assistance.

 

[Fluff]

How many points should player 3 & 4 get?

 

[joerenaud]

How many points should player 3 & 4 get?

They would split the 175 points (100 for third + 75 for fourth) or each would earn 87.5 points. There can be three or even four people tied sometimes. Thank you.

 

[swapnilk]

Enter the undernoted formula in G2 and drag down:

=AVERAGE(OFFSET($J$1,F2,0, COUNTIF($F$2:$F11,F2)))

 

[Fluff]

Another option is

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J
1	Flight	Name	Gross	Hdcp	Net	Place Finished	Points Earned		Place	Prize
2	A	Player 1	80	10	70	1	150		1	150
3		Player 2	84	9	75	2	125		2	125
4		Player 3	87	10	77	3	87.5		3	100
5		Player 4	84	7	77	3	87.5		4	75
6		Player 5	85	7	78	5	70		5	70
7		Player 6	88	9	79	6	65		6	65
8		Player 7	84	4	80	7	60		7	60
9		Player 8	90	9	81	8	55		8	55
10		Player 9	91	8	83	9	50		9	50
11		Player 10	94	8	86	10	45		10	45
12									11	40
13									12	35
14									13	30
15									14	25
16									15	20
17									16	15
18									17	10
19									18	5
20									19	0
Sheet4

Cell Formulas
Range	Formula
F2:F11	F2	=RANK(E2,$E$2:$E$11,1)
G2:G11	G2	=AVERAGEIFS(J:J,I:I,">="&F2,I:I,"<="&COUNTIFS(F:F,F2)+F2-1)

 

[joerenaud]

Thank you to both for the solutions. They both work beautifully. Appreciate the prompt responses.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[joerenaud]

After using both versions, I notice one tiny typo in swapnilk's formula:

=AVERAGE(OFFSET($J$1,F2,0, COUNTIF($F$2:$F11,F2)))

It should be: =AVERAGE(OFFSET($J$1,F2,0, COUNTIF($F$2:$F$11,F2))) Or at least that's what I did to use it.

Otherwise there's problems with pasting by dragging down.

 

[Fluff]

One word of warning about that formula, Offset is volatile & so will calaculate far more often than is needed & could cause your workbook to slow down.