Rolling Total Calculation v. Pivot Table

[Quillcon]

Excel 2007:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Color	% Red	% Blue	1/1/13	2/1/13	3/1/13	4/1/13	5/1/13	6/1/13	7/1/13		6 Month Rolling Total	6 Month % Red	6 Month % Blue
2	Orange	.5		3	6	2	5	4	2	6			=L2*B2	=L2*C2
3	Purple	.5	.5	7	2	3	12	4	15	6			=L3*B3	=L3*C3
4	Green		.5	4	4	14	10	1	8	11			=L4*B4	=L4*C4

<tbody>
</tbody>

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