Golf Handicap Using Last 5 Scores


G

Guest

I am trying to handicap a golf league using only the last five scores posted.
This handicap needs to change on a weekly basis as this information is fed to
other programs. We begin with an entering ave (last years ending ave) and use
this average for as many times necessary until the golfer has 5 rounds
played. We use the beg. ave. four times plus one actual score to determine
the new average after week one (if the player golfed). After two rounds are
played, we use the beg. ave three times plus the two actual scores to find
the new average. Once 5 rounds are played, these five rounds are used for
that weeks new handicap. After 6 rounds are played, I use the last 5 scores
only for the new average.

Can anybody help with this?

Rick

Scores Beg Ave Week 1 Week 2 Week 3 Week 4 Week 5 Week 6
Golfer 1 82 85 78 83 84 86
Golfer 2 90 87 84 93 94 91
Golfer 3 85 86 85 80
Golfer 4 81 79 78 80 76

Average
Golfer 1 82 82.6 81.8 82 82 82.4 83.2
Golfer 2 90 90 90 90 70.2 71 71.2
Golfer 3 85 85 85 85 85.2 85.2 84.2
Golfer 4 81 80.6 80 79.8 79.8 78.8 78.8
 
Ad

Advertisements

S

Sandy Mann

Rick,

Assuming that the Beginning Average is in cell B2, with the weekly scores
following on in that row then in say cell B10 enter =B2

To get the averages start in C2 and enter:
=IF(C2<>"",((COLUMN($G$2)-COLUMN())*$B$2+SUM($C$2:C2))/5,"")

and copy along to G10

In H10 enter the formula:
=IF(H2="","",SUM(D2:H2)/5)
and copy along the row as far as required.

HTH

Sandy
 
G

Guest

Jason

Thanks for your help, I had to change your formula to read "Sum(C2*" instead
of "Sum(B2*".

Thanks again, it works great
 
G

Guest

Thanks
That seems to work in rows 2&3 for golfers 1&2, but when I do it for golfer
3 I get the #value. This is probably due to him only playing three rounds.

Any ideas?
 
Ad

Advertisements

G

Guest

This will calculate the correct average at any time. With the golfer's name
in B2, last year's avg. in C2, and scores going from D2 to last cell in row 2
(I stopped at J2), use:

=IF(COUNT(C2:J2)<5,SUM(B2*(5-COUNT(C2:J2)),C2:J2)/5,SUM(J2:INDEX(C2:J2,MATCH(LARGE(IF(C2:J2<>"",COLUMN(C2:J2)),5),COLUMN(C2:J2),0)))/5)

Array-entered, meaning press ctrl + shift + enter. Change J2 if needed.

HTH
Jason
Atlanta, GA
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top