Creating a 12MTD calculation

[Rhian]

Can somebody help please, i am trying to work out a formula which will give me a 12MTD figure.

I have got data stretching from June 2013 to current month and i need to show what the 12 MTD avergae figure is as i add data in month on month.

This needs to be a formual as manually calculating the average for each row would be time consuming.

Any ideas??

 

[CROY1985]

Hi rhian

Try this for the average:

=AVERAGE(INDIRECT(ADDRESS(ROW(),COLUMN(C4)+COUNTA(C4:AG4))&":"&IF(COUNTA(C4:AG4)<12,ADDRESS(ROW(),COLUMN(C4)),ADDRESS(ROW(),COLUMN(C4)+COUNTA(C4:AG4)-12))))

This is based on your first month of data being in cell C4, and the data running to CG4 (Dec-15), change those parts accordingly

 

[CROY1985]

I should add that this assumes cells are empty after the month you have just added the data in.

 

[Rhian]

I should add that this assumes cells are empty after the month you have just added the data in.

Thank you