variable average

[Sue in Illinois]

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

 

[Bob Phillips]

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

 

[Sue in Illinois]

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]

EOMONTH is from the Analysis Toolpak (ATP). Do you have it installed (check
Tools>Addins)?