Working on a customized Golf Handicap Calculator

I have a question regarding averaging numbers to determine handicaps in a golf league I am in. The complication stems from the various possibilities for determing the avereage score to be used. Here is the logic I would like to encode in Excel:

Players average score used in determinining a handicap will incorporate the previous four rounds played. Absent weeks do not get scored and do not get counted as a week played. For example:

Wk1 53
Wk2 Absent
Wk3 52
Wk4 Absent
Wk5 56
Wk6 54
Wk7 Absent
Wk8 52

I would like the logic to be able to figure the average of the last four rounds actually played from the data given. In this case, it would average Wks 8, 6, 5, and 3 to yield an answer of 53.5. The next player may have played all 8 weeks and thus their average should be based on Wks 5-8 and I'd like the same coding to be able to be applied to all players.

Thank you...that seems to be working. I just need to incorporate it throughout each of the weeks throughout the spreadsheet.

Thanks again!

I ran into a snag and Im not sure what is going on. The coding worked for the most part but I am not getting desirable results consistently. Im not sure if I am misusing the logic or what. Here is an example of where this is not working:
A B C D
1 Week Score
2 1 55
3 2 57
4 3 55
5 4 47
6 5
7 6 56

I have the equation in C1 as:

=AVERAGE(B8:INDEX(B1:B8, LARGE(ROW(B1:B8) * ISNUMBER(B1:B8), 4)))

The result yields a correct result (53.75.

However, when I use the spreadsheet I created which of course has many more players and looks different than here I get the wrong result. This is what my actual sheet looks like (a portion of it anyways):

A B C D
1 Player No. 1 2
2 Week Dave Kay
3 Previous 46 55
4 1 46 57
5 2 48 55
6 3 47 47
7 4 48 absent
8 5 46 56
9 6

25 Average
26 Wk1
27 Wk2
28 Wk3
29 Wk4
30 Wk5
31 Wk6

In my actual sheet, I have Cell D31 with the following logic:

=AVERAGE(D8:INDEX($D$2:D8, LARGE(ROW($D$2:D8) * ISNUMBER($D$2:D8), 4)))

This yields 52.667 in D31 which is the average of Weeks 2,3, and 5. I need it to yield the average of Weeks 1, 2, 3, and 5 since Week 4 was not played. The result should be 53.75 (similar to the simple example above). Im not sure why in this instance the logic is not working since the simple example does indeed work as I intended...it averaged the last four numbers in that column...ignoring the blanks or "non number" cells. The actual sheet Im using, however, seems to be averageing the last four cells regardless of whether there is a number in there.

Any ideas on what I am missing here?

This coding seems to address the issue. Thank you! One thing I may have mislead you on was what numbers need to be calculated for each week's average. For instance, Week 8 average should be calculated from the previous 4 weeks played NOT including Week 8 scores itself. I took your coding above and put it in cell D29 but then I changed the range in all locations from D7 to D6 for that cell to accomplish this. That may also have been why we were getting different "should be" results.

Thanks again for your help on this. This was exremely helpful!