Last 12 month's average

Hello,

I am trying to create a formula at the end of a row that will give an average for the past 12 months data.

Simple enough I thought, however I am having difficulty in making the formula change each time I add a new month.

I would like to add an extra month column to the sheet and have the average include only the new month and the previous 11 without having to manually change it.

Attached is a document showing some of the data which will hopefully help in resolving my problem. I am using Excel 2013

Any help would be great, thanks

In M3 and filled down, try

=AVERAGE(OFFSET(M3,0,-12,1,12))

Brilliant, thank you Jonmo1. That has done the trick nicely!

And thank you Kevin, that also works!

Two options for one post. Brillskills.

Originally Posted by Kevin UK

Hi simonlblea

Try the Index & Match (Non volatile)

Formula:

Please Login or Register  to view this content.

Or

Formula:

Please Login or Register  to view this content.

If you need to exclude zero values when averaging.

Wouldn't this always include A3, so it becomes 13 cells when inserting 1 column.
Then 14 after another column is inserted, then 15 etc..

Originally Posted by simonlblea

Brilliant, thank you Jonmo1. That has done the trick nicely!

Great, glad to help out.

Hi all,

Jonmo's solution works brilliantly on my spreadsheet however now I am trying to make some simple line charts and of course they are not updating when I add a new column!

Any thoughts on how to solve this?

Make a Named Range, say MyData or whatver.
The Refers to for that range is
=OFFSET(M3,0,-12,1,12)

Then for the chart, use that named range

Thank you, would I need to do anything differently to get the dates to update as well. Have tried creating a named range for those, however when I enter the name on the 'Select Data' button it just comes up as 'Roling_average' (see attached)

Make 2 named ranges (one for the dates and one for the values) like the hard coded ranges you put inthe 2nd chart.

Also need to use the $ to lock the cell reference.

MyDates : =AVERAGE(OFFSET($M$1,0,-12,1,12))
MyVals: =AVERAGE(OFFSET($M$3,0,-12,1,12))

Then modify the chart to use those names instead of the hard coded ranges.

Sorry for taking ages to reply. Everything is working now as I wanted it. Thank you for your help.