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


--
anley
------------------------------------------------------------------------
anley's Profile: http://www.excelforum.com/member.php...o&userid=37471
View this thread: http://www.excelforum.com/showthread...hreadid=571316

How to create a rolling 6 month average?
 

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

How to create a rolling 6 month average?
 

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!


--
anley
------------------------------------------------------------------------
anley's Profile: http://www.excelforum.com/member.php...o&userid=37471
View this thread: http://www.excelforum.com/showthread...hreadid=571316

How to create a rolling 6 month average?
 

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!