Rolling Month formula

[Injinia]

Hi,


I have data running Jan 11 to current Jun 12. I have thus far being calculating the rolling month sum, manually.


May data is as follows:

B1:Z1 = Jan 11 to Dec 12

B2:Z2 = Figures for Sales


In cell Z3, I have the rolling 12 month sum. If I update the cell with March sales, the rolling sum will be March 11 - Feb 12, if I update April 11, it will be April 11 - March 12 and so on and so forth.


Thanks in advence.


-Injinia

 

[nazmul_muneer]

Your question is not clear to me

can you send a sample file?

 

[Injinia]

Unfortunately, I have never uploaded a file before, I dont know how to do it.


I will give it another shot, hopefully u get it clear now:

2 rows of data:


B1:Z1 = Jan 11 to Dec 12

B2:Z2 = Figures for Sales


at the end of the figures for sales, so (AA2; not Z3 as mentioned earlier) I would like the sum of the last 12 months of the data.


so if I input this month's sales figures, the sum in AA2 should be from last year Jul to this year Jun(12 months). If I then update next month's figures, the sum shall be last year Aug to this year Jul(12 months) and so on.


Clearer?


-Injinia

 

[kuldeepjainesl]

Assuming Starting date in A1 and Rolling month in B1:B12. Try this =DATE(YEAR($A$1),MONTH($A$1)+B1,1)


=="A"=========="B"

Jan-10==========1

1-Feb-10========2

1-Mar-10========3

1-Apr-10========4

1-May-10========5

1-Jun-10========6

1-Jul-10========7

1-Aug-10========8

1-Sep-10========9

1-Oct-10========10

1-Nov-10========11

1-Dec-10========12


Regards,

 

[Hui]

Injinia

I think you want:

=SUM(OFFSET($A$2,,COUNTA($A$2:$Z$2)-11,1,12))

And as for uploading files refer: http://chandoo.org/forums/topic/posting-a-sample-workbook

 

[Injinia]

Thanks ya'll,


Hui's formula works excellent.


-Injinia