Moving Annual Total

[afc10]

I have Column A as "Date" and Column B as "Customers" (Number of customers in my store on the given date).

I want Column C to be a MAT or Moving Annual Total of the Customer numbers.

If Column A is dated "10 Jun 2002" then the MAT would be the sum of customers from "11 Jun 2001" to "10 Jun 2002". On the 11-Jun-2002 it would sum fom 12-Jun-2001 to 11-Jun-2002. etc etc etc.

I've been trying to use "DSum" but inspite of every reasonable approach I've taken I can't get a result.

Any thoughts please on where I might find a solution.

Thanks

 

[Aladin Akyurek]

On 2002-06-09 03:50, afc10 wrote:
I have Column A as "Date" and Column B as "Customers" (Number of customers in my store on the given date).

I want Column C to be a MAT or Moving Annual Total of the Customer numbers.

If Column A is dated "10 Jun 2002" then the MAT would be the sum of customers from "11 Jun 2001" to "10 Jun 2002". On the 11-Jun-2002 it would sum fom 12-Jun-2001 to 11-Jun-2002. etc etc etc.

I've been trying to use "DSum" but inspite of every reasonable approach I've taken I can't get a result.

Any thoughts please on where I might find a solution.

Thanks

Lets A2 to be the first date entry, B2 the number of customers on the date in A2.

In C2 enter and copy down:

=SUMIF(OFFSET($A$2,0,0,CELL("Row",A2)-1,1),">="&EDATE(A2,-12)+1,OFFSET($B$2,0,0,CELL("Row",A2)-1,1))

In order to use EDATE you need to activate the Analysis Toolpak add-in via Tools|Add-Ins.

See the figure how all this looks like:

aaDynSUMIF afc10.xls
	A	B	C	D
1	Date	Customers	MAT
2	11-Jun-01	8	8
3	15-Jun-01	9	17
4	18-Jun-01	10	27
5	10-Jun-02	20	47
6	12-Jun-02	13	52
7	15-Jun-02	18	61
...

Aladin