Rolling Averages

[Guest]

I have an excel spread sheet in column A I have week commencing dates for a
whole year and column B contains the sales data for each of these weeks. I
need excel to give me a continual rolling average for the latest 4 weeks
entered, so that when I enter another week the first week "drops" out and a
new average is calculated.
I am being told that this enters into the realms of programming and nearly
impossible to do! Can anyone help?

Charlotte
X

 

[T. Valko]

This will average the last 4 numbers in column B assuming the data is
entered as a contiguous block. Numbers start in cell B2.

=IF(COUNT(B:B)<4,"",AVERAGE(OFFSET(B2,COUNT(B:B)-1,,-4)))

If there aren't at least 4 numbers to average the formula returns a blank
cell.

 

[MartinW]

Hi Charlotte

Assuming your data starts in row 2
Put this in C5
=AVERAGE(B2:B5)
and drag it down as far as needed, the cell references
will update as you drag.

If you want to drag it past the end of your data to allow for
future input use
=IF(B5="","",AVERAGE(B2:B5)

HTH
Martin