Rolling Total Calculation v. Pivot Table

[Quillcon]

Excel 2007:

[TABLE="width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[TD]K[/TD]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Color[/TD]
[TD]% Red[/TD]
[TD]% Blue[/TD]
[TD]1/1/13[/TD]
[TD]2/1/13[/TD]
[TD]3/1/13[/TD]
[TD]4/1/13[/TD]
[TD]5/1/13[/TD]
[TD]6/1/13[/TD]
[TD]7/1/13[/TD]
[TD][/TD]
[TD="align: center"]6 Month Rolling Total[/TD]
[TD="align: center"]6 Month % Red[/TD]
[TD="align: center"]6 Month % Blue[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]Orange[/TD]
[TD="align: center"].5[/TD]
[TD="align: center"][/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]=L2*B2[/TD]
[TD]=L2*C2[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Purple[/TD]
[TD="align: center"].5[/TD]
[TD="align: center"].5[/TD]
[TD="align: center"]7[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]15[/TD]
[TD="align: center"]6[/TD]
[TD][/TD]
[TD][/TD]
[TD]=L3*B3[/TD]
[TD]=L3*C3[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Green[/TD]
[TD="align: center"][/TD]
[TD="align: center"].5[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]14[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]11[/TD]
[TD][/TD]
[TD][/TD]
[TD]=L4*B4[/TD]
[TD]=L4*C4[/TD]
[/TR]
</tbody>[/TABLE]


I want to add a 6 month rolling total formula to column L. What is the formula if a column is added at K (or whatever it happens to be in future months) for new data? There will be a blank column between data and calculations. I want the rolling total to include the newly added column and the previous 5 months of data (rolling total for August 2013 would include 3/1/13 through 8/1/13).

How would I set up a pivot table to give me the same information?

 

[AhoyNC]

See if this formula works for you.
Assumes there will always be a one column between last date & rolling total column, plus you will use insert to add the next date column.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Color	% Red	% Blue	1/1/2013	2/1/2013	3/1/2013	4/1/2013	5/1/2013	6/1/2013	7/1/2013		6 Month Rolling Total	6 Month % Red	6 Month % Blue
2	Orange	0.5		3	6	2	5	4	2	6		25
3	Purple	0.5	0.5	7	2	3	12	4	15	6		42
4	Green		0.5	4	4	14	10	1	8	11		48
Sheet