Try this array formula** :

=IF(COUNT(B2:S2),AVERAGE(IF(COLUMN(B2:S2)=LARGE(I F(B2:S2,COLUMN(B2:S2)),MIN(COUNT(B2:S2),5)),IF(B2: S2,B2:S2))),"")

How do you want to handle decimal returns? Average 88, 87, 82 =
85.666666666667

This version** will round to the nearest whole number:

=IF(COUNT(B2:S2),ROUND(AVERAGE(IF(COLUMN(B2:S2)=L ARGE(IF(B2:S2,COLUMN(B2:S2)),MIN(COUNT(B2:S2),5)), IF(B2:S2,B2:S2))),0),"")

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

--
Biff
Microsoft Excel MVP


"JoeM" wrote in message
news:B4aSj.1234$Bd1.973@trndny09...
I would greatly appreciate help with my handicap spreadsheet.

Here's the setup: players listed in column A, scores for 18 weekly games
in columns B (the first week of the season) through S (the last week of
the season). I need a formula that will calculate, for a given player,
the average of his 3 scores (if he only has three scores), or the average
of his four scores (if he only has four scores), or the average of his
_most recent_ five scores (if he has five or more scores). The formula
must take into account that a given player may not play every week. Cells
corresponding to the missed weeks would be empty.

Thanks!