Golf Handicap Using Last 5 Scores

[Golf League Schedule]

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

 

[Biff]

Hi!

Here's a sample file that may help you:

http://www.xl-logic.com/pages/formulas.html

Scroll down to item 27.

If after you study that file and still need help just post back.

Biff

 

[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

 

[Golf League Schedule]

Jason

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

Thanks again, it works great

 

[Golf League Schedule]

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?

 

[Jason Morin]

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