Extracting latest 20 dates and then 10 lowest values related to those dates - Golf Handica

Hi all,

I've been a member for a long time and have gained alot of solutions from other posts; but I'm trying to find a more efficient way to compute golf handicaps or more correctly a golf index. A golf index for any golfer is computed by taking their last 20 scores and from those 20, choose the lowest 10 scores and then average those 10 and multiply that average by .96. Technically, with each score there is an associated differential calculation and this is the number that I need the lowest 10 of the last 20. I have a table of everyone's scores with the dates and the associated differentials, and can create a pivot table to only choose the latest 20 dates (by filtering). Once i have these 20 scores for each guy, I'm at a loss on how to extract his lowest 10 differentials from these 20 scores. It would be great to be able to show which rounds are being used for the calculation as this can change every time a new round is played. Any help is greatly appreciated.

Hi

Using sheet Indexes of your example file then
K6: =SMALL(G6:G26,1)
K7: =SMALL(G6:G26,2)
K8: =SMALL(G6:G26,3)
....
K15: =SMALL(G6:G26,10)

This will give you the 10 smallest scores. You can then copy this block down to the first row of each competitor.

Does that help?

rylo

Rylo,

Thank you for this option. I did try this before but when someone has less than 20 rounds, the from-to parameters of the =small function would have to verified that they were the correct beginning and ending coordinates. Since the pivot can extract the last 20 scores by filtering, is there a way to use another pivot from this pivot that extracts the lowest 10 differentials? Apparently, in a single pivot table, there isn't a way to perform multiple filtering?

Thanks

Hi

Can you put up a sample of your raw (ie pre pivot table) data. Make sure you have more than and less than 20 entry items.

If there is <20 items for a competitor, then how is the handicap worked out?

rylo

Rylo,

In the workbook, there is another tab named "Indexes" where the raw data is coming from for the pivot table. Several players have at least 21 rounds and the filter in the pivot table on the date column is what is allowing me to only extract the latest 20 from the "indexes" table. Remember, to compute the handicap, you are ultimately choosing the best 10 rounds out of the last 20 rounds played. If a player has less than 20, it's ok, we still only use the lowest 10 scores.
Thanks.

Hi

OK, see how this goes.

Select sheet Indexes

Select the range B5:J396 and sort by Last (ascending), First (ascending), Date (descending). This sorting is critical.
Select L6, and insert a defined name with the name ScoreRng, and the refers to:

Please Login or Register  to view this content.

Still in L6 array enter the formula (ctrl, shift, enter)

Please Login or Register  to view this content.

Copy down from L6 to L396

This should (I hope) give you the required number for the first appearance of each person.

HTH

rylo

Rylo,

Sorry for the delay in getting back to you, massive computer meltdown at work where my files are. Anyway, tried the array formula in it's not working and it seems to be referring to a blank area of the spreadsheet for the part $A$1..$A$10. Could you try the formula and then reattach the spreadsheet for my learning. I can't seem to attach a spreadsheet now.

Thanks,

chris

Chris

Here's the file.
915956.xlsx

rylo