Trying to Pick 10 lowest golf scores from a list of scores and average for Handicaps

[1wxman1]

Need help
I have a list of golf scores and need to pick 10 lowest and then average them for Handicap. Can you point me in the correct direction?

[MarvinP]

Hi 1wxman1 and welcome to the forum,

We need to see how you've saved your data. We can do this problem but need an example of the layout of the names and numbers to give our best advice.
You can attach a sample workbook by editing your post and then click on "Go Advanced" then on the Paper Clip Icon above the advanced message area.

[1wxman1]

See attached spreadsheet for data

[FlameRetired]

I retained a copy of your original sheet in sheet2.

Put this formula in I200 and wherever applicable:

Please Login or Register  to view this content.

and fill down. Apply to the rest of the tables with addresses adjusted accordingly.

Your file is attached with example on sheet1 highlighted.

Our figures differ on what the ten lowest are. I came up with the 10th lowest to be 27.1; there is a 27.6 included in the

your uploaded file (I200:I219).

Hope this helps.

[daddylonglegs]

Do you need to actually identify the 10 lowest? How do you identify which 10 when there are ties?

You can actually get your handicap quite simply without needing to explicitly identify which entries are the lowest, i.e. with this formula

=AVERAGE(SMALL(H200:H219,{1,2,3,4,5,6,7,8,9,10}))*0.96

[MarvinP]

Hey 1wxman1,

I have a little better answer than daddylonglegs above. I think you want to use half of the posted scores if the player has less than 20. This formula will do that:

Please Login or Register  to view this content.

Find attached your sheet with my formulas in it. You will need to enter the yellow cell formulas using a CSE or Control+Shift+Enter keystroke combination as they are array formulas.

I really think you should change the structure of your scores to a single table with the player name in column A. When the number of scores gets greater than 20 the problem gets harder, as the date column comes into play. The "last 20 rounds" makes this a much harder problem. I'll keep working on it to see if I can make it easier than what you have already.

[1wxman1]

thanks, appreciate your help

[1wxman1]

We only use last twenty scores, so I roll off the oldest as we get a new score.

[MarvinP]

Hi 1wxman1,

I have a better answer for you now. It took daddylonglegs to show me the way. He is a lot better with Excel than us mortals.

Here is the plan and example on my sheet2 attached. You simply add the most current rounds to the bottom of the list of people on sheet2 and their handicaps will be calculated correctly in the top section.

Here are some of the features of Sheet2 scheme.
1. I have Dynamic Named Ranges on Sheet2 that will grow or shrink based on how many rows are added to the list.
2. I've named these DNR's "Names", "Dates", "Diffs" (DLL doesn't use the "Dates" in his formula.)
3. You need to add new rounds played to the bottom of the list on sheet2 and sort the list from Oldest to Newest.
4. The ARRAY formula in cell B2 (and D2) needs to be entered using a CSE (Control Shift Enter) keystroke maneuver.

If you use Sheet2 layout you can make a AutoFilter table of the rounds played and filter or sort them to show players what you have for them.
You won't need to keep track of separate tables for each player if you put the player's name in column A.
You will be able to add new players to your group by simply adding their new name and round info to the bottom the list.

This is the answer I was hoping to give you and think it will work much better than separate tables for each player. See the attached.