3 Month Moving Average Dashboard

Hi ALL,

I'm trying to create a dashboard based on historical data to analyze using charts and trendlines. I'm using Excel 2007. The new Excel 2010 has functionality of using sparklines which make life more easier but we don't have it. Hence trying to use charts & trendlines instead.

Attachment is the sample file with data in tab "Data". I was able to create few report in "Report 1" & "Report 2" tabs and want to be able to create similar to "Sample Report" tab. The report shows current month average, previous month average and 3 month moving average data by region and countries for type of updates we make. But I'm strugling with 3 month moving average. Can we do this report in Pivot table format as well.

Any help is highly apprciated.

Excel Lover,
Duke

I am not sure if a 3 month "moving" average is possible with pivots. But maybe if you can assist in understanding the data in terms of what do you want to average out and what parameters the calculations should be based on, we can try and help you here.

arlu1201,

Thank you for your quick response. What I'm trying to achieve is based on two parts:
Part I: In tab "Report 1", in cell B1 & B2 there's dropdown for Month and Type (Type is type of server updates in the department.) Based on those options, it will give an average of % Type completed calculated in column G in "Data" tab for the selected month (cell B1) & Type of updates (cell B2) for various regions (cell A5, A7, A9 & A11). It will also display previous month average and 3 month moving average as well. I managed to calculate the average % Type completed for selected month and previous month but can't calculate 3 month moving average.

Part II: Refer "Sample Report" tab Calculate current month, previous month and 3 month moving average by month selected from the drop-down, Type and country.

I hope it helps.
Thanks Again for your time.