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

That's simple.
A1:E1: DATES
A1:D1: Date1, Date2, Date3, Date4...
A2:D2: 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.
--
HTH
Tomek Polak, http://vba.blog.onet.pl

Thanks Topola - that was a great help :-)

"topola" wrote:

That's simple.
A1:E1: DATES
A1:D1: Date1, Date2, Date3, Date4...
A2:D2: 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.
--
HTH
Tomek Polak, http://vba.blog.onet.pl

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