Rolling 24-month Averaging

One of our govt. agencies requires that I compute the 24-month rolling averages of the daily average input to a manufacturing process. In this instance, the agency is demanding the rolling average since 2003, so there are a fair number of data points involved. The process control computer can provide me the daily data (attached file contains 30 months of data), but cannot compute the 24-month rolling average and nothing in the Excel HELP feature helps!
Thanks. 1brickshy

Here's one idea:

If as implied you are using XL2007 you could change JB's

C7: =(SUMIF($A:$A,">"&C$4,$B:$B)-SUMIF($A:$A,">"&C$5,$B:$B)) / (COUNTIF($A:$A,">="&C$4)-COUNTIF($A:$A,">"&C$5))

to

C7: =AVERAGEIFS($B:$B,$A:$A,">"&C$4,$A:$A,"<="&C$5)
(limit the ranges from entire columns if possible)

note however AVERAGEIFS is not backwards compatible so if you intend or are obliged to run on earlier versions then

a) stick with the original approach
b) note that use of EOMONTH in month headers will require activation of Analysis ToolPak Add-In else revert to a standard DATE formula, eg:

D5: =DATE(YEAR(C5),MONTH(C5)+2,0)

etc...

If that takes care of your need, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].


(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)