Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
variable average
I've got a spreadsheet linked to Access which pulls in a year's worth of
actual sales numbers (of products). I need to figure the average sales over either the past 3 or 6 or 12 months. The number of months to base the average on is part of the query. Is there a way to write the "=average" formula to look to the cell w/ the number of months to use and then average on the past 3/6/12 months based on what's in that cell? I can send a sample spreadsheet to anyone who thinks they can help. thanks much, Sue in Illinois |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
variable average
Try this array formula
=AVERAGE(IF(B2:B200=EOMONTH(TODAY(),-F1-1)+1,A2:A200)) where F1 holds the number of months. Commit the formula with Ctrl-Shift-Enter, not just Enter -- __________________________________ HTH Bob "Sue in Illinois" wrote in message ... I've got a spreadsheet linked to Access which pulls in a year's worth of actual sales numbers (of products). I need to figure the average sales over either the past 3 or 6 or 12 months. The number of months to base the average on is part of the query. Is there a way to write the "=average" formula to look to the cell w/ the number of months to use and then average on the past 3/6/12 months based on what's in that cell? I can send a sample spreadsheet to anyone who thinks they can help. thanks much, Sue in Illinois |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
variable average
Bob
Got the #NAME? error - Column E holds the "number of months to look back" and the actual quantities for 2008 run from Column F thru Column Q, with the average trend running from Column R thru Column AC (Jan-Dec, 2009). If the number of months in Col E is 3, then starting w/ R the formula needs to average O/P/Q; and in Col S average P/Q/R. I've got the formula in all columns & just changed the cell range. My need is that if the boss wants to change the number of months from 3 to 6, I want to be able for one of us to just change the number in Col E and have the formula recalculate - rather than having to go thru Cols R thru AC & change the range. Thanks for your quick response! Sue Hallwas "Bob Phillips" wrote: Try this array formula =AVERAGE(IF(B2:B200=EOMONTH(TODAY(),-F1-1)+1,A2:A200)) where F1 holds the number of months. Commit the formula with Ctrl-Shift-Enter, not just Enter -- __________________________________ HTH Bob "Sue in Illinois" wrote in message ... I've got a spreadsheet linked to Access which pulls in a year's worth of actual sales numbers (of products). I need to figure the average sales over either the past 3 or 6 or 12 months. The number of months to base the average on is part of the query. Is there a way to write the "=average" formula to look to the cell w/ the number of months to use and then average on the past 3/6/12 months based on what's in that cell? I can send a sample spreadsheet to anyone who thinks they can help. thanks much, Sue in Illinois |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
variable average
EOMONTH is from the Analysis Toolpak (ATP). Do you have it installed (check
ToolsAddins)? -- __________________________________ HTH Bob "Sue in Illinois" wrote in message ... Bob Got the #NAME? error - Column E holds the "number of months to look back" and the actual quantities for 2008 run from Column F thru Column Q, with the average trend running from Column R thru Column AC (Jan-Dec, 2009). If the number of months in Col E is 3, then starting w/ R the formula needs to average O/P/Q; and in Col S average P/Q/R. I've got the formula in all columns & just changed the cell range. My need is that if the boss wants to change the number of months from 3 to 6, I want to be able for one of us to just change the number in Col E and have the formula recalculate - rather than having to go thru Cols R thru AC & change the range. Thanks for your quick response! Sue Hallwas "Bob Phillips" wrote: Try this array formula =AVERAGE(IF(B2:B200=EOMONTH(TODAY(),-F1-1)+1,A2:A200)) where F1 holds the number of months. Commit the formula with Ctrl-Shift-Enter, not just Enter -- __________________________________ HTH Bob "Sue in Illinois" wrote in message ... I've got a spreadsheet linked to Access which pulls in a year's worth of actual sales numbers (of products). I need to figure the average sales over either the past 3 or 6 or 12 months. The number of months to base the average on is part of the query. Is there a way to write the "=average" formula to look to the cell w/ the number of months to use and then average on the past 3/6/12 months based on what's in that cell? I can send a sample spreadsheet to anyone who thinks they can help. thanks much, Sue in Illinois |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Average: 2nd argument as a variable in another cell av(c6:c(a3)) | Excel Discussion (Misc queries) | |||
Average the last eight or nine (Variable) numbers in a row. | Excel Discussion (Misc queries) | |||
How to average one constant # to variable #s? A1:A2 A1:A3 A1:A4 | Excel Worksheet Functions | |||
average last 17 cells (variable) | New Users to Excel | |||
average last 17 cells (variable) | New Users to Excel |