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" wrote:

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<"",COLU MN(C2:J2)),5),COLUMN(C2:J2),0)))/5)

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

HTH
Jason
Atlanta, GA


"Golf League Schedule" wrote:

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