Hello everyone.
Please can someone help me figure out how to get a 12 month average by using a formula or VBA.
I have columns Jan, Feb, Mar, etc, starting from say column E, and I need column D to sum 12 months from whatever month we're in.
Example file attached if I'm not making any sense. Thanks for helping. :))
Ted
Hi Ted,
Following formula sum the last 12 entries when the list start in A1:
=SUM(INDIRECT(ADDRESS(MAX(1,COUNT(A:A)-11),1)&":"&ADDRESS(COUNT(A:A),1)))
You need to adjust it to fit Your own situation.
Thanks Dennis, I've played around with it a bit - but sometimes no data gets entered in a month, and in that case the above wouldn't really work because it picks up the last 12 entries. ie: I need it to move along a column month by month even if no data is entered.
Hi Ted
Try this page: http://www.ozgrid.com/Excel/DynamicRanges.htm and see: Expand Down One Row Each Month
As you need to go across Columns your would look something like:
=OFFSET(Sheet1!$A$1,0,0,1,MONTH(TODAY()))
Thanks Dave, exactly what I needed.
Don’t have an account yet? Register yourself now and be a part of our community!