Formula Help

[Corkster]

I am scoring a golf game where the best score on a hole gets 7, 2nd best gets 5, 3rd 3 and worst 1. If there are any ties they split the sum of those 2,3 or 4 places. For example if 2 tie the best they each get 6, if 2 tie the worst they each get 2, if 3 tie for the best they each get 5, if 2 tie the 2nd they each get 4, and so on. If all 4 tied each would get 4 points. Basically, 16 points per hole get allocated depending on results.

What formula will accomplish this?

This golfer sure appreciates your help.

 

[T. Valko]

I am scoring a golf game where the best score on a hole gets 7, 2nd best gets 5, 3rd 3 and worst 1. If there are any ties they split the sum of those 2,3 or 4 places. For example if 2 tie the best they each get 6, if 2 tie the worst they each get 2, if 3 tie for the best they each get 5, if 2 tie the 2nd they each get 4, and so on. If all 4 tied each would get 4 points. Basically, 16 points per hole get allocated depending on results.

What formula will accomplish this?

This golfer sure appreciates your help.

Try this...

Create this 2 column table of the point distribution:

Book1
	G	H
1	Rank	Points
2	1	7
3	2	5
4	3	3
5	4	1
Sheet1

Then, your scorecard might look something like this:

Book1
	A	B	C	D
1	Name	Score	Rank	Points
2	Bubba	5	4	1
3	Phil	2	1	6
4	Jim	2	1	6
5	Fred	4	3	3
Sheet1

Enter this formula in C2 and copy down to C5 to get the score rank:

=RANK(B2,B$2:B$5,1)

Enter this formula in D2 and copy down to D5 to get the points:

=AVERAGE(OFFSET(H$2,MATCH(C2,G$2:G$5)-1,,COUNTIF(C$2:C$5,C2)))