Another golf handicap question

J

JoeM

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!
 
T

T. Valko

Try this array formula** :

=IF(COUNT(B2:S2),AVERAGE(IF(COLUMN(B2:S2)>=LARGE(IF(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)>=LARGE(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)
 
J

JoeM

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
 
J

JoeM

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

Thanks!

Joe
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top