golf score rankings

[jimrward]

I have the following where I am tracking our sunday hack around a golf course, I currently have it all working so that I can get the top 3 scores for the 3 players and also the best ever score for a hole over the season believe its called eclectic or something like that

I use SUMPRODUCT for the ranking with no duplicates because of the multiple criteria, and then append the rank to the players name so I can use INDEX and MATCH to lookup JIM1, JIM2, JIM3 etc

I would like to add in a slight twist, as it currently stands it will RANK them in chronological order for tied scores, so if I match this score more than 3 times, it will only show the older ones in the top3 leader board, I would like to show the most recent 3

I cant think of a way of adding date into the sumproduct to get the more recent ones

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Name	Date	1	2	3	4	5	6	7	8	9	Total	Hcap	Net	*	*
2	Callum	19/09/2010	6	8	5	7	7	5	9	5	8	60	35	25	6	Callum6
3	Callum	26/09/2010	11	6	5	5	9	4	9	8	9	66	35	31	13	Callum13
4	Jim	26/09/2010	5	4	5	3	4	3	8	4	5	41	14	27	5	Jim5
5	Norman	26/09/2010	4	4	4	3	4	3	5	4	3	34	4	30	5	Norman5
6	Callum	17/10/2010	6	11	8	5	11	7	11	7	9	75	35	40	21	Callum21
7	Norman	17/10/2010	5	4	4	2	4	3	6	6	4	38	4	34	12	Norman12
8	Callum	21/11/2010	7	9	5	5	6	6	9	8	7	62	35	27	9	Callum9
9	Jim	21/11/2010	6	6	7	3	6	4	6	3	6	47	14	33	18	Jim18
Horsham

second half of the spreadsheet showing results etc, in reality I had a score of 40, on the 15th nov, 14th Nov, 3rd Aug, but it does not show 15th Nov, I would like it to show Jim2 as 15/11/11, and Jim3 as 14/11/11

Excel Workbook
	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE
1	Name	Course	*	1	2	3	4	5	6	7	8	9	Total	*
2	*	Horsham	Par	4	4	4	3	4	3	4	3	4	33	*
3	*	*	*	337	233	231	113	255	129	383	107	273	2061	*
4	*	*	*	323	212	226	103	236	117	369	97	262	1945	*
5	*	*	*	*	*	*	*	*	*	*	*	*	*	*
6	Callum	*	Best	6	5	5	3	5	4	6	3	6	43	*
7	*	30/10/2011	1	8	5	6	4	5	6	9	6	7	56	1
8	*	02/04/2011	2	7	6	6	5	6	7	10	4	6	57	2
9	*	03/08/2011	3	9	7	7	5	6	6	8	3	6	57	2
10	*	*	*	*	*	*	*	*	*	*	*	*	*	*
11	Jim	*	Best	4	4	4	2	3	2	4	3	3	29	*
12	*	15/11/2011	1	5	5	4	5	5	2	5	3	5	39	1
13	*	03/08/2011	2	5	6	7	4	3	2	6	3	4	40	3
14	*	14/11/2011	3	4	4	4	2	6	4	5	4	7	40	3
15	*	*	*	*	*	*	*	*	*	*	*	*	*	*
16	Norman	*	Best	4	3	2	2	4	2	3	3	3	26	*
17	*	31/12/2010	1	5	4	3	3	4	2	4	3	3	31	1
18	*	02/04/2011	2	4	4	2	3	4	3	5	3	4	32	1
19	*	09/01/2011	3	4	4	3	2	5	4	5	3	3	33	2
Horsham

 

[Dryver14]

In a standard eclectic once you score better on a hole than you did the last time only the one best score counts so if Jim scores a 4 on week 1 and a three on week 2 on hole 1 only the three counts

there will probably be 5 to 7 rounds.

Of course there is a weekly winner based on the score for the day but it is not his final score for a week that counts only the sum of his best scores

 

[jimrward]

the eclectic I am not bothered about, its the overall tally of best regular rounds, with the top 3 in recent date order

 

[jimrward]

ok, solved it I think, few beers makes the difference, I have added an extra column R where I take the Total and add TODAY-<the date>/10000, to give a decimal, so the first entry becomes 60.0427 since it was 427 days ago. I then rank on that using the original sumproduct formula

so column R is

=L2+(TODAY()-B2)/10000 and copied down

ranking is now using column R

=IF(ISNUMBER(L2),SUMPRODUCT(--(A$2:A$600=A2),--(R$2:R$600<=R2))-SUMPRODUCT(--(A2:A$600=A2),--(R2:R$600=R2))+1,"")

results are

Excel Workbook
	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF
11	Jim		Best	4	4	4	2	3	2	4	3	3	29
12		15/11/2011	1	5	5	4	5	5	2	5	3	5	39	1
13		15/11/2011	2	5	6	5	3	4	4	5	3	5	40	3
14		14/11/2011	3	4	4	4	2	6	4	5	4	7	40	3
Horsham

 

[Dryver14]

Glad to see you got it sorted JIm,

I was just making the point that I know more about Golf than Excel or VBA