Question on Calculating Moving Sum

[wally1723]

Hey everyone,
Just looking for some quick help on how to calculate a moving sum: Let’s say that I have 2 years of sales information broken up by months. Is there a way for excel to recognize every time I add a new month’s worth of data and automatically calculate the sum of that cell the previous 11 cells? (ex. Add March 2010 sales data, have excel total and update yearly sales with all records dating back to March 2009à Once I add April 2010 Sales data, have excel automatically total and update yearly sales dating back to April 2009.)
The long and short of it is I need to know how to have excel recognize brand new data that is entered into the right most empty column and automatically add it the previous 11 months worth of data. From what ive been researching it seems to be some sort of combination with the Offset function and the Count Function? Any help would be much appreciated. Thanks!

 

[Mike Blackman]

Hi and welcome to the board,

Create a Name (Insert-Name-Define) and give it a sensible name - I've called mine SheetHeight and in the formula bar...

=MATCH(9.99999999999999E+307,Sheet1!$A:$A)

*Changing Sheet1 for the sheet name your altering.

And then create a second Named Range - I've called mine MovingSum...

=INDEX(Sheet1!$A:$A,SheetHeight-11):INDEX(Sheet1!$A:$A,SheetHeight)

Then from any cell you can use this formula to get the result...

=SUM(MovingSum)

Hope this helps

 

[sulakvea]

everything else aside (i.e. the fact that you need excel also to recognize which year the month belongs to) - you can just use SUM(B:B). this will sum up everything in Col B, including everything that will be added over time.

re: the question about summing up the sales by year. say your Dates sit in Col A, and your Sales numbers sit in Col B.

in any empty cell, like C1, type this:

=SUMPRODUCT((YEAR(A1:A10000)=2010)*(B1:B10000)) - this will sum up sales for 2010

 

[Mike Blackman]

Or using the same methodology this 1 off formula can be used...

=SUM(INDEX(A:A,MATCH(9.99999999999999E+307,A:A)-11):INDEX(A:A,MATCH(9.99999999999999E+307,A:A)))

 

[sulakvea]

i think i misunderstood - are you looking at only latest 12 months worth of sales? regardless of which years they belong to

if so, again assuming that your sales sit in Col B, here is the formula:

=SUM(INDIRECT("B" & COUNTA(B:B)-11 & ":B" & COUNTA(B:B)))

you can change B to any other column to suit your needs.

 

[Mike Blackman]

Hi Sulakeva,

I think I'd avoid volatile functions such as INDIRECT where possible as they can have a drastic effect on formula heavy workbooks and I'm not sure you should place all your trust in COUNTA as should there be a blank in the column anywhere be it on purpose or by accident your range wont be correct.

 

[wally1723]

Thanks alot Mike and Sulakvea for responding so quickly...

Mike, the 1 off equation you gave me worked perfectly... thanks alot for your help
Sulakvea, t he second equation you worked and was much more what I was looking for from your first post, sorry if the wording was a little confuisng...thanks for your help on the problem

 

[sulakvea]

good call Mike, i agree about COUNTA - if there are blanks, it will screw up the results. seems unlikely from the OP, but still a very real possibility.

But I disagree about INDIRECT - form the post it seems like a relatively simple task (looking at sales / dates only), with a single entry (just one formula to calculate the latest 12 months). almost impossible to mess it up, unless you try to on purpose.

 

[Mike Blackman]

We have no idea what else is in the workbook, there could be tabs and tabs of array formula for all we know and it only takes one volatile function to endlessly re-trigger a recalc of the whole workbook.

It is a slim chance but any chance is too much chance as far as i'm concerned.

 

[Mike Blackman]

See here.