Help, I'm so confused.....

[Guest]

Help, I’m a newbie and thought I had this figured out, but I don’t….What I’m trying to do is keep a running average for numbers in a spread spreadsheet. This question has several parts, so bear with me. Below shows the manually calculated answers

……….G……………….H……………….....I……………......J………...….K……....….L….
…..Gross Score…..Round HCP…Running HCP…..Net Score…..Points…..Best 4.
4……..87…………….12.72………….12.72…………....74.28……....74.28………….
5…….103……………30.70………….21.71………...….81.29…...…155.57………….
6…….103……………30.13………….24.52………...….78.48……....308.33…………
7…….103……………29.34………….25.72…………....77.28……....385.61……385.61
8……...93……………21.42………….24.86…………....68.14……...453.75……298.1
9……..100…………...24.02………….24.72…………....75.28…..….529.03……294.9

Part 1: Column G shows the gross golf score. Column H shows the handicap as calculated for each round. Column I is the running handicap, i.e. since G4 was the first score inputted, I4 will be the same as H4. I5 is the average between H4 and H5. I6 is the average between H4, H5, H6, etc, etc. So basically, I9 is the sum of H4:H9/6. I can’t figure out how to get it to average like I’ve got it set up above. A formula that would replicate itself and keep a running average in Column I that automatically updates every time I enter info in Column G & H would be very helpful

Part 2: Column J gives the net score, which is basically taking the running handicap (Column I) and subtracting it from the gross score in Column G, i.e. G9 (100) – I9 (24.72) = J9 (75.28). What I need for Column K, is a running total of the scores listed in column J, i.e. K8 (453.75) + J9 (75.28) = K9 (529.03)

Part 3: We are keeping track of our 4 best net scores in Column L, so using my figures above, after 4 rounds, it would have been J4:J7. After 5 rounds, it would be J4, J6, J7, & J8. After 6 rounds, it would be J4, J7, J8, & J9. Is there any way for this to work

I would like for these to be updated automatically each time I enter a new round. Any suggestions would be helpful.

Thanks in advance

 

[Frank Kabel]

Hi Kevin
Part 1:
enter the following in I4
=SUM($H$4:$H4)/(ROW()-3)

Part 2:
one way:
- in K4 enter: =J4
- in K5 enter: =K4+J5
- copy down
another way:
enter the following in K4: =SUM($J$4:$J4)

Part 3: For this I'd use the function SMALL
enter the following in L7 (before there is now best of 4):
=SMALL($J$4:$J7,1)+SMALL($J$4:$J7,2)+SMALL($J$4:$J7,3)+SMALL($J$4:$J7,4
)
copy down

HTH
Frank

 

[Guest]

Frank, that worked awesome. Thank you.

one more problem For our little golfing group, the handicap system works fine, however, some of the guys want the handicaps to be as close to USGA rules as possible. The actual rules are pasted below. Would I use a combination of the Part 1 and Part 3 answers to arrive at the solution? Basically, if they golf under 5 or 6 rounds, we'd use the lowest handicap round. The more they golf, the more handicap rounds are used until you reach 20 rounds and then you just use the last 20 rounds. Is there some kind of forumula that can be used to adjust each person's handicap depending on the number of rounds they play. Here's the formula we are supposed to be using (the differential it talks about has already been computed in Column H (Round HCP). If it's too much of a hassle to figure it out, don't sweat it. The answers you have already provided work out great, just wanted to see if I could fine tune it. Thanks again!

The procedure for calculating Handicap Indexes is as follows:

Use the table below to determine the number of handicap differentials to use;
Number of
Acceptable Scores Differentials
To Be Used
5 or 6.............Lowest 1
7 or 8............ Lowest 2
9 or 10.......... Lowest 3
11 or 12.......... Lowest 4
13 or 14 .........Lowest 5
15 or 16......... Lowest 6
17 ..................Lowest 7
18.................. Lowest 8
19.................. Lowest 9
20................. Lowest 10

Determine handicap differentials; **(Column H)

Average the handicap differentials being used;

Multiply the average by .96;

Delete all numbers after the tenths’ digit. Do not round off to the nearest tenth.

Example 1: Fewer than 20 scores (11 scores available).

Total of lowest 4 handicap differentials: 104.1
Average (104.1 / 4): 26.025
Multiply average by .96: 24.984
Delete digits after tenths: 24.9 (THIS I CAN DO WHEN I FORMAT THE CELLS)
USGA Handicap Index: 24.9

 

[Frank Kabel]

Hi Kevin
USGA golf rules and that shortly after I get my golf allowance
(required in Germany if you want to play on a golf course) and my
handicap is really bad :-(

1. Determine the number of scores used for calculating the average: For
this create a seperate worksheet with a lookup table for number of
scores and relevant scores for calculating. e.g. on the sheet
Calc_Handicap enter the following
A B
5 1
7 2
9 3
11 4
....
20 10

Now use the following formula to get the number of relevant scores:
=VLOOKUP(´ROW()-3,'Calc_Handicap!'$A$1:$B$10,2,1)
store this in a seperate columns (lets say I). So enter this formula in
I8 and copy down

2. Now get the relevant scores/average. Put the following in J8 and
copy down
=ROUND((SUMIF($H$4:$H8,"<" & SMALL($H$4:$H8,I8+1))/J8)*0.96,1)

These formulas are not fully tested (as I don't have enough testing
data) but should work. If you have problems with that, you may send me
your spreadsheet and I'll set-up the initial formulas

Frank

 

[Frank Kabel]

sorry a small typo. Replace
=ROUND((SUMIF($H$4:$H8,"<" & SMALL($H$4:$H8,I8+1))/J8)*0.96,1)
with
=ROUND((SUMIF($H$4:$H8,"<" & SMALL($H$4:$H8,I8+1))/I8)*0.96,1)

Frank

 

[Gord Dibben]

Kevin

Now all you have to do is figure out your Handicap from the Course Rating and
Slope Rating at your course.

Following is an example for determining a differential using an adjusted gross
score of 95 made on a course with a USGA Course Rating of 71.5 and a USGA
Slope Rating of 125:

Adjusted Gross Score - USGA Course Rating: 95 - 71.5 = 23.5
Difference x Standard Slope: 23.5 x 113 = 2655.5
Result / USGA Slope Rating: 2655.5 / 125 = 21.24
Handicap Differential (rounded): 21.2

Perhaps the differentials you gave are already factored for Course and Slope.
In that case, ignore rest of post.

Gord Dibben Excel MVP

 

[Guest]

Thanks Gord, that's one thing I'm not confused about I did send Frank a copy of my workbook, cause I'm apparently doing something wrong and can't get the first formula he gave me to work. He can probably look over the mess I sent him and figure it out hopefully :

 

[Frank Kabel]

Thanks Gord, that's one thing I'm not confused about I did send
Frank a copy of my workbook, cause I'm apparently doing something
wrong and can't get the first formula he gave me to work. He can
probably look over the mess I sent him and figure it out hopefully

Workbook is on its way
Frank