Sum last 12 or 24 values

[SimonHughes]

Hello, I have a column of monthly sales in the range D2:D90 (next month it will be D2:D91 and so on). I want to sum and average the last six, 12, 18 months ect but cannot work out the formula. The first formula will need to sum cells D85:D90. Any help would be appreciated, many thanks

I am working on Excel 2010

 

[ukmikeb]

Perhaps :-
SimonHughes

	D	E	F	G	H	I
85	6
86	5
87	4
88	3
89	2		Last 6	Last 12	Last 18	Last 24
90	1		21	78	171	300
91			3.50	6.50	9.50	12.50

<tbody>
</tbody>

Excel 2007

Worksheet Formulas Cell Formula F90 =SUM(OFFSET($D$1,COUNT($D:$D),,-COLUMNS($F:F)*6,1)) G90 =SUM(OFFSET($D$1,COUNT($D:$D),,-COLUMNS($F:G)*6,1)) H90 =SUM(OFFSET($D$1,COUNT($D:$D),,-COLUMNS($F:H)*6,1)) I90 =SUM(OFFSET($D$1,COUNT($D:$D),,-COLUMNS($F:I)*6,1)) F91 =AVERAGE(OFFSET($D$1,COUNT($D:$D),,-COLUMNS($F:F)*6,1)) G91 =AVERAGE(OFFSET($D$1,COUNT($D:$D),,-COLUMNS($F:G)*6,1)) H91 =AVERAGE(OFFSET($D$1,COUNT($D:$D),,-COLUMNS($F:H)*6,1)) I91 =AVERAGE(OFFSET($D$1,COUNT($D:$D),,-COLUMNS($F:I)*6,1)) <tbody> </tbody>

<tbody>
</tbody>

SimonHughes

	D	E	F	G	H	I
85	6
86	5
87	4
88	3
89	2		Last 6	Last 12	Last 18	Last 24
90	1		31	82	169	292
91	16		5.17	6.83	9.39	12.17

<tbody>
</tbody>

Excel 2007

Worksheet Formulas Cell Formula F90 =SUM(OFFSET($D$1,COUNT($D:$D),,-COLUMNS($F:F)*6,1)) G90 =SUM(OFFSET($D$1,COUNT($D:$D),,-COLUMNS($F:G)*6,1)) H90 =SUM(OFFSET($D$1,COUNT($D:$D),,-COLUMNS($F:H)*6,1)) I90 =SUM(OFFSET($D$1,COUNT($D:$D),,-COLUMNS($F:I)*6,1)) F91 =AVERAGE(OFFSET($D$1,COUNT($D:$D),,-COLUMNS($F:F)*6,1)) G91 =AVERAGE(OFFSET($D$1,COUNT($D:$D),,-COLUMNS($F:G)*6,1)) H91 =AVERAGE(OFFSET($D$1,COUNT($D:$D),,-COLUMNS($F:H)*6,1)) I91 =AVERAGE(OFFSET($D$1,COUNT($D:$D),,-COLUMNS($F:I)*6,1)) <tbody> </tbody>

<tbody>
</tbody>

Data series 1 to 89 reversed in D2:D90 in first table.

hth

 

[Aladin Akyurek]

Hello, I have a column of monthly sales in the range D2:D90 (next month it will be D2:D91 and so on). I want to sum and average the last six, 12, 18 months ect but cannot work out the formula. The first formula will need to sum cells D85:D90. Any help would be appreciated, many thanks

I am working on Excel 2010

Something like:

In F3 enter...

=SUM(OFFSET(INDEX(D:D,MATCH(9.99999999999999E+307,D:D)),0,0,-F2))

where F2 houses a value like 6 (12, 18, etc.).

 

[SimonHughes]

Thanks Mike, the worksheet formula works well. I have just tweaked the formula to suit my worksheet layout but the offset works just fine, many thanks

 

[SimonHughes]

ADVERTISEMENT

Hi Aladin, yes, that also works perfectly. Is there any specific benefit in using one or the other formula? Many thanks.

 

[Aladin Akyurek]

Hi Aladin, yes, that also works perfectly. Is there any specific benefit in using one or the other formula? Many thanks.

The formula with OFFSET and INDEX\MATCH evaluates from the last value up and won't be affected by the presence of in-between blanks.

 

[SimonHughes]

Thanks for the explanation. In this scenario either will work as well as each other then.