Rolling totals

  • Thread starter Thread starter DTH99
  • Start date Start date
D

DTH99

I have some data I need to analyse over a number of periods on a rolling basis.

Cell A1 i select a month, cells B1:B12 contain months Jan-Dec, cells C1:C12
contain sales values in that month. Cell D1 shows the last 3 months sales,
cell E1 shows the last 6 months sales and F1 shows last 12 month sales.

What I want to be able to do is change the month in A1 and the rolling
totals in D1, E1 & F1 automatically based on the date in A1

Any help would be appreciated.
 
In D1
=SUM(OFFSET($A$1,MATCH($A$1,$B:$B,0)-1,2,3,1))

In E1
=SUM(OFFSET($A$1,MATCH($A$1,$B:$B,0)-1,2,6,1))

When you say "last 6 month sales" in cell E1 and "last 12 month sales" in F1
you should have the previous years data from cell B13 downwards....


If this post helps click Yes
 
Back
Top