Formula for a Rolling 12 month Average

**dandavis1** · 03-16-2010, 09: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, 10:09 AM

Hi,

In AC37

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

**dandavis1** · 03-18-2010, 10: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, 05:03 AM

like this?

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

**greyscale** · 06-22-2010, 07: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, 08: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?

**BBea** · 03-04-2014, 05:37 PM

New to the forum and I think I am trying to so similar. I want to show columns with 13 months of data with a total for Current YTD and 12 Month Total. I will have an actual row showing my 12 mo. moving average for the particular stat for that month. Somewhere in the spreadsheet is a driver key so if I type "7" it would recognize I am in July of the current year and therefore need Jan-July for YTD and Aug - July for 12mo. Additionally, this number would tell everything to shift over one column and make room for a new month's data. Years ago I had this type of worksheet and now I am needing it again. Has anyone seen this worksheet out there so I'm not starting from scratch?

**jes2539** · 05-04-2018, 06:04 PM

I have a 12 month rolling sum for a budget. However, some of the budget has negative values resulting in the formula as a #N/A. Does anyone know how to resolve this issue?Untitled.png

**protonLeah** · 05-04-2018, 06:24 PM

jes2539,
Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.