I feel like you've opened up a whole new area of possibilities in Excel for
me.

Thanks!

Joe


"T. Valko" wrote in message
...
See this:

http://www.cpearson.com/Excel/ArrayFormulas.aspx

--
Biff
Microsoft Excel MVP


"JoeM" wrote in message
news:kJaSj.8726$1m3.6599@trndny02...
This is fantastic - thanks! I will be nesting your formula inside of
some additional calculations and rounding will be the final (outer)
calculation. I have to say, I have read about array formulas but don't
really understand them. Do you know of a web site or other source of
information that would help me to learn how to use them?

Thanks again.

Joe


"T. Valko" wrote in message
...
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!