Calculating a rolling sum in Excel 2007

[Elle G]

Hi,


I'm new to the Excel board, but I'm hoping you guys can help me out. I need to find a way to calculate employees' sales for the last 3 months on a rolling basis. I think I want a function that looks for the column that matches "current month," offsets by -2 columns, and then sums from that column to the current month. Every month I add a new column of data, and I've been manually updating the formula to sum the correct columns. (I do this calculation for the last 6 and 12 months, too, so once I have the 3 months formula working, I can modify it for them.)


I've spent the past few hours trying various combinations of match, index, hlookup, and offset to no avail. Maybe it's possible to start the offset from whatever column is to the left of "Total sales" because that will always be the latest month?


Thanks in advance for any help!


Sample data:


[TABLE="width: 500"]
<tbody>[TR]
[TD]Name[/TD]
[TD]Feb 13[/TD]
[TD]Mar 13[/TD]
[TD]Apr 13[/TD]
[TD]May 13[/TD]
[TD]Jun 13[/TD]
[TD]Jul 13[/TD]
[TD]Aug 13[/TD]
[TD]Sep 13[/TD]
[TD]Oct 13[/TD]
[TD]Total sales[/TD]
[TD]Sales last 3 months[/TD]
[/TR]
[TR]
[TD]George[/TD]
[TD]8[/TD]
[TD]12[/TD]
[TD]6[/TD]
[TD]8[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]7[/TD]
[TD]75[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]Tom[/TD]
[TD]9[/TD]
[TD]7[/TD]
[TD]5[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]12[/TD]
[TD]8[/TD]
[TD]6[/TD]
[TD]9[/TD]
[TD]75[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]Mark[/TD]
[TD]9[/TD]
[TD]9[/TD]
[TD]8[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]8[/TD]
[TD]10[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]84[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]Stewart[/TD]
[TD]7[/TD]
[TD]10[/TD]
[TD]6[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]12[/TD]
[TD]11[/TD]
[TD]9[/TD]
[TD]11[/TD]
[TD]85[/TD]
[TD]?[/TD]
[/TR]
[TR]
[TD]Current month[/TD]
[TD]Oct 13[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[AhoyNC]

See if this works.
Note there are two commas after the -3 in the formula.

Excel Workbook
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Name	13-Feb	13-Mar	13-Apr	13-May	13-Jun	13-Jul	13-Aug	13-Sep	13-Oct	Total sales	Sales last 3 months
2	George	8	12	6	8	10	11	5	8	7	75	20
3	Tom	9	7	5	9	10	12	8	6	9	75	23
4	Mark	9	9	8	10	11	8	10	9	10	84	29
5	Stewart	7	10	6	9	10	12	11	9	11	85	31
6
Sheet

 

[Elle G]

That is so much simpler than what I was trying to do, and it works perfectly! Thank you so much.

The actual data is really wide, so I ended up replacing the "total sales" column with a column that repeats the list of employees. I used the same offset formula and can now see the list of names right next to sales for last 3, 6, and 12 months. HURRAH.

 

[AhoyNC]

You're welcome. Thanks for the feedback and welcome to the forum.