Another golf handicap question
 

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!

Another golf handicap question
 

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!

Another golf handicap question
 

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!

Another golf handicap question
 

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!

Another golf handicap question
 

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!

Another golf handicap question
 

Good deal. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"JoeM" wrote in message
news:bthSj.3305$Bd1.3134@trndny09...
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!