golf score rankings

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,784
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
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
ABCDEFGHIJKLMNOP
1NameDate123456789TotalHcapNet**
2Callum19/09/20106857759586035256Callum6
3Callum26/09/2010116559498966353113Callum13
4Jim26/09/20105453438454114275Jim5
5Norman26/09/2010444343543344305Norman5
6Callum17/10/201061185117117975354021Callum21
7Norman17/10/20105442436643843412Norman12
8Callum21/11/20107955669876235279Callum9
9Jim21/11/201066736463647143318Jim18
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
RSTUVWXYZAAABACADAE
1NameCourse*123456789Total*
2*HorshamPar44434343433*
3***3372332311132551293831072732061*
4***323212226103236117369972621945*
5**************
6Callum*Best65535463643*
7*30/10/20111856456967561
8*02/04/201127665671046572
9*03/08/20113977566836572
10**************
11Jim*Best44423243329*
12*15/11/20111554552535391
13*03/08/20112567432634403
14*14/11/20113444264547403
15**************
16Norman*Best43224233326*
17*31/12/20101543342433311
18*02/04/20112442343534321
19*09/01/20113443254533332
Horsham
 
Last edited:

Some videos you may like

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
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

Well-known Member
Joined
Feb 24, 2003
Messages
1,784
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
the eclectic I am not bothered about, its the overall tally of best regular rounds, with the top 3 in recent date order
 

jimrward

Well-known Member
Joined
Feb 24, 2003
Messages
1,784
Office Version
  1. 2016
  2. 2013
  3. 2011
  4. 2010
  5. 2007
Platform
  1. Windows
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
STUVWXYZAAABACADAEAF
11JimBest44423243329
1215/11/20111554552535391
1315/11/20112565344535403
1414/11/20113444264547403
Horsham
 

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
Glad to see you got it sorted JIm,

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

Watch MrExcel Video

Forum statistics

Threads
1,127,272
Messages
5,623,752
Members
415,986
Latest member
C_Braga

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top