How to create a rolling 6 month average?

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

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))
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"anley" wrote:
>
> 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
>
>
> --
> anley
> ------------------------------------------------------------------------
> anley's Profile: http://www.excelforum.com/member.php...o&userid=37471
> View this thread: http://www.excelforum.com/showthread...hreadid=571316
>
>

Max

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

Thanks a lot and have nice night!

Glad it worked out fine for you <g>!
Thanks for calling back ..
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"anley" wrote:
> Max
> You say 'one way', well you only need a way that works and what you
> suggested works perfectly.
> Thanks a lot and have nice night!

Max
how would I need to alter the formula to do a 3 month rolling average

please and thank you

David
Florida