Calculation using rolling 4 week amounts

[Guest]

Folks
A colleague has developed a spreadsheet into which he inputs numbers over 4
weeks. These numbers are added together and divided by 4 to give an average
amount. this average amount is used in a number of other calculations around
the spreadsheet.

Is it possible to write a formula which will automatically add the next
week's amount, and drop off the previous 4th week? If not, any ideas what
the best solution will be? He is currently changing everything manually.

Eg
weeks might be
31/10/05 07/11/05 14/11/05 21/11/05

how can he automatically drop off 31/10/05 and include 28/11/05.

Thanks for all your help
Janet

 

[topola]

That's simple.
A1:E1: DATES
A11: Date1, Date2, Date3, Date4...
A22: Val1, Val2, Val3, Val4...

$A$4=COUNT(A1:E1)-4 or
$A$4=COUNT(DATES)-4

Moving 4-Period Formula:
=SUM(OFFSET(A2,0,$A$4,1,4))

Remark: Insert New Column BEFORE last column which is E.

 

[Guest]

Thanks Topola - that was a great help

 

[topola]

I just saw it. Simpler method:
=SUM(OFFSET($E$2,0,-4,1,4))
Isn't Excel funny? TP