Formula for a Rolling 12 month Average

**dandavis1** · 03-16-2010, 10:33 AM

See Attached.

The YTD 10 column is fine that will change everytime you update each month. I was wondering if there was a formula I could use for the Rolling 12 month Column.

At the moment it is averging from Mar 09-Feb10 which is correct but when it comes to putting in data for Mar 10, the Rolling 12 month column will obviously not change.

Is there a way of moving the formula along one each time you input data into a new month. So when Mar 10 has been entered the rolling 12 month column will pick up Apr-09-Mar-10 then when Apr 10 has been entered the formula will change again to May-09-APr-10?

Many Thanks....

**sweep** · 03-16-2010, 11:09 AM

Hi,

In AC37

=AVERAGE(OFFSET(D37,0,MATCH(-1,D37:AA37,-1)-1,1,-12))

**dandavis1** · 03-18-2010, 11:39 AM

Thanks very much for your help, this is working perfectly. If I wanted to add another column at the end called current month, would there be a similar formula where it would just show that current months data, then when you enter the next months data it would then show that data in the current month column.

Many thanks for your help!

**sweep** · 03-19-2010, 06:03 AM

like this?

=OFFSET(D37,0,MATCH(-1,D37:AA37,-1)-1)

**greyscale** · 06-22-2010, 08:50 PM

I can't get this to work for me. I have columns AC - AN containing 12 months of data. column AO has the 12 month average. I wish to insert a column at AO each month which would move the average to column AP so that I have the average of the most recent 12 months. However, the formula is not updating and is still averaging AC-AN when I need it to average AD-AO. Can anyone help?

**greyscale** · 06-22-2010, 09:28 PM

I just realised that if I insert all blank columns and then hide them (as per original attachment) the formula works beautifully. I am concerned, however, that this will be prone to user error. Does anyone know how to modify the formula so that the moving average calc would be fixed in the first column (e.g. column C on the original attachment) and the calc would return the last 12 populated columns?