| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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! |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
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! |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Golf Handicap Anomoly.... | Excel Worksheet Functions | |||
| golf handicap | Excel Discussion (Misc queries) | |||
| golf handicap | Excel Discussion (Misc queries) | |||
| Golf Handicap Using Last 5 Scores | Excel Worksheet Functions | |||
| Golf Handicap | Excel Worksheet Functions | |||
Linear Mode
