Rolling 6 & 12 month variance (dynamic formula)

[MDuff]

I have this table of data that will grow as time passes (new months data will be added)
******** ******************** ************************************************************************>

Microsoft Excel - Fiscal Forecasting Template v2.2mikesupdate.xlsx ___Running: 12.0 : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
E1F1G1H1C2D2E2F2G2H2C3D3E3F3G3H3C4D4E4F4G4H4C5D5E5F5G5H5C6D6E6F6G6H6C7D7E7F7G7H7C8D8E8F8G8H8C9D9E9F9G9H9C10D10E10F10G10H10C11D11E11F11G11H11C12D12E12F12G12H12C13D13E13F13G13H13 =
	C	D	E	F	G	H
1	Average	2005	2006	2007	2008	2009
2	46,177	41,993	52,767	47,662	42,284	35403
3	46,724	40,657	53,470	49,205	43,565	36672
4	45,608	41,287	52,915	46,775	41,454	34801
5	44,548	40,716	50,669	45,052	41,755	0
6	42,248	39,786	49,314	41,216	38,676	0
7	43,126	38,630	49,989	43,675	40,209	0
8	41,285	39,421	47,692	40,104	37,921	0
9	48,363	53,979	51,587	45,925	41,959	0
10	48,826	54,049	51,295	47,605	42,355	0
11	48,476	56,055	50,400	45,380	42,070	0
12	42,431	50,254	43,415	39,269	36,786	0
13	39,997	48,853	41,840	36,284	33,009	0
Monthly

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.



I have this formulas to give me a rolling 6 and 12 month variance what I have to do now if update them by hand as new data is added anyone know of a way to make them Dynamic so as new data is added the old one drops off and the new data takes it place

I am open to a formula or VBA

Thanks in advance


******** ******************** ************************************************************************>

Microsoft Excel - Fiscal Forecasting Template v2.2mikesupdate.xlsx ___Running: 12.0 : OS = Windows XP
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
E31E32 =
	B	C	D	E
31	Rolling 6 Month Growth			-4.88%
32	Rolling 12 Month Growth			-9.19%
Monthly

[HtmlMaker 2.42] To see the formula in the cells just click on the cells hyperlink or click the Name box
PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.

[tusharm]

Since you have data until Mar 2009 why does your analysis only extend to Aug 2008?

Also, a dynamic formula would be a lot easier if your data were laid out in a single table with year in one column, month in the next, and the amount in a third.

[sanrv1f]

try this
enter the month (Aug-08) , from which you want to look back, in a cell

say D31 here

for 6 months

 
=(SUMPRODUCT(--(DATEVALUE($B$2:$B$13&$D$1:$H$1)<=$D$31),--(DATEVALUE($B$2:$B$13&$D$1:$H$1)>=DATE(YEAR($D$31),MONTH($D$31)-5,1)),$D$2:$H$13)-
SUMPRODUCT(--(DATEVALUE($B$2:$B$13&$D$1:$H$1)<=DATE(YEAR($D$31),MONTH($D$31)-6,1)),--(DATEVALUE($B$2:$B$13&$D$1:$H$1)>=DATE(YEAR($D$31),MONTH($D$31)-11,1)),$D$2:$H$13))/
SUMPRODUCT(--(DATEVALUE($B$2:$B$13&$D$1:$H$1)<=DATE(YEAR($D$31),MONTH($D$31)-6,1)),--(DATEVALUE($B$2:$B$13&$D$1:$H$1)>=DATE(YEAR($D$31),MONTH($D$31)-11,1)),$D$2:$H$13)

for 12 months

 
=(SUMPRODUCT(--(DATEVALUE($B$2:$B$13&$D$1:$H$1)<=$D$31),--(DATEVALUE($B$2:$B$13&$D$1:$H$1)>=DATE(YEAR($D$31),MONTH($D$31)-11,1)),$D$2:$H$13)-
SUMPRODUCT(--(DATEVALUE($B$2:$B$13&$D$1:$H$1)<=DATE(YEAR($D$31),MONTH($D$31)-12,1)),--(DATEVALUE($B$2:$B$13&$D$1:$H$1)>=DATE(YEAR($D$31),MONTH($D$31)-23,1)),$D$2:$H$13))/
SUMPRODUCT(--(DATEVALUE($B$2:$B$13&$D$1:$H$1)<=DATE(YEAR($D$31),MONTH($D$31)-12,1)),--(DATEVALUE($B$2:$B$13&$D$1:$H$1)>=DATE(YEAR($D$31),MONTH($D$31)-23,1)),$D$2:$H$13)

[sanrv1f]

forgot to mention,

you need to have month names as row titles, for this formula to work

	B	C	D	E	F	G	H
1		Average	2005	2006	2007	2008	2009
2	Jan	46177	41993	52767	47662	42284	35403
3	Feb	46724	40657	53470	49205	43565	36672
4	Mar	45608	41287	52915	46775	41454	34801
5	Apr	44548	40716	50669	45052	41755	0
6	May	42248	39786	49314	41216	38676	0
7	Jun	43126	38630	49989	43675	40209	0
8	Jul	41285	39421	47692	40104	37921	0
9	Aug	48363	53979	51587	45925	41959	0
10	Sep	48826	54049	51295	47605	42355	0
11	Oct	48476	56055	50400	45380	42070	0
12	Nov	42431	50254	43415	39269	36786	0
13	Dec	39997	48853	41840	36284	33009	0

[MDuff]

Thanks