help with 12 week rolling average

[Dominick03]

In rows I have inventory tracked weekly. I am trying create a formula
that will give me an average of my last 12 weeks. Such that when I
add a new column for the new weeks inventory level the formula will
give the average of that week and the last 11.

example:

a1 b1 c1 d1 e1 f1 g1 h1 i1 j1 k1 l1 12 week
average (a1:l1)
1 2 3 4 5 6 7 8 9 10 11 12
6.5

next week

a1 b1 c1 d1 e1 f1 g1 h1 i1 j1 k1 l1 m1 12 week
average (b1:m1)
1 2 3 4 5 6 7 8 9 10 11 12 13 7.5


I have tried working with the following formula but have had no
luck.

=AVERAGE(OFFSET(A1,,COUNT(A1:L1)-12,,12))

Any help would be appreciated. Thanks

 

[T. Valko]

Your formula will work if you expand the range to allow for future addition
of data:

=AVERAGE(OFFSET(A1,,COUNT(A1:Z1)-12,,12))

Here's another way without using the volatile function, Offset:

=AVERAGE(Z1:INDEX(A1:Z1,COUNT(A1:Z1)-12+1))

I'm assuming there are at least 12 values to average and there are no
empty/blank cells within the range.

Biff

 

[MartinW]

Hi Dominick,

Another way to approach it is to put this in L2

=IF(L1="","",AVERAGE(A1:L1))
Then drag it across as far as is needed.

Now as you add data in M1,N1,O1 etc. your floating 12 week
average will appear in the cell beneath it.

HTH
Martin

 

[Dominick03]

Thanks guys now it's working