How to create a rolling 6 month average?

[anley]

Hiya everyone.

How can I do a rolling average that automatically adds a new value when
I create a new row? What I mean is this.

I keep a very simple spreadsheet of my monthly business sales which is
updated at the end of every month.

I want to keep track of a rolling 6 month average.

Yes, I could manually change the cells (to calulate the average over
the last 6 months) but how could I do this automatically? For example,
right now the last month of the spreadsheet has July's data in in and
then immediatly below this is the 6 month average.

Feb $1000
Mar $1267
Apr $1574
May $990
Jun $1258
July $1500

6 month ave = $1265

But next month I'll want to 'add a row' so I can place the August
figure in, and this is the point - how can I get the Formula to
AUTOMATICALLY add the August figure and the other previous 5 months.

Feb $1000 (THIS FIGURE IS NOW DROPPED FROM THE AVERAGE)
Mar $1267
Apr $1574
May $990
Jun $1258
July $1500
Aug $1421 (THIS FIGURE IS NOW AUTOMATICALLY INCLUDED IN THE AV)

6 month av = $1335

Hope I've made myself clear! Thanks

 

[Guest]

One way ..
Assuming your average formula is currently in cell B8
(averaging the data in B2:B7),
try instead in B8:
=AVERAGE(OFFSET($A$8,-1,1,-6))

 

[anley]

Max

You say 'one way', well you only need a way that works and what yo
suggested works perfectly.

Thanks a lot and have nice night

 

[Guest]

Glad it worked out fine for you <g>!
Thanks for calling back ..