What is the proper method to calculate a 3 month rolling average for each respective product model? Looking for a formula, not a pivot table solution. Data entry will be done on a weekly basis therefore the formula should take that into account. User data will continue to expand on a weekly basis for calculation done on monthly basis. Excel file attached. Thx
Last edited by tangomj; 06-09-2013 at 07:20 PM.
TO get the average of the last 3 months for each listed item, perhaps this, in F2 then copied down:
=AVERAGEIFS(C:C, A:A, "<="&TODAY(), A:A, ">="&TODAY()-90, B:B, E2)
_________________
Microsoft MVP 2010 - Excel
Visit: Jerry Beaucaire's Excel Files & Macros
If you've been given good help, use theicon below to give reputation feedback, it is appreciated.
Always put your code between code tags. [CODE] your code here [/CODE]
?None of us is as good as all of us? - Ray Kroc
?Actually, I *am* a rocket scientist.? - JB (little ones count!)
Does the Attached file fit the bill? I noticed that the file you sent is in a pre-2007 format. AVERAGEIFS is only available in 2007 and later. Hopefully you are able to open .xlsx files.
3-Month.xls
Cell H3 is an array formula that creates a list of unique items used within the past 3 months. That array formula is then filled down.
Use this in F2 and drag down.
Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks