help with 12 week rolling average
 

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

help with 12 week rolling average
 

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

wrote in message
ups.com...
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

help with 12 week rolling average
 

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

help with 12 week rolling average
 

Thanks guys now it's working

On Jan 29, 2:56 am, "MartinW" wrote:
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