Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to create a rolling 6 month average? | Excel Discussion (Misc queries) | |||
rolling average | Excel Worksheet Functions | |||
Macro to copy cells to rows below | Excel Discussion (Misc queries) | |||
Macro to insert copied cells | Excel Discussion (Misc queries) | |||
52 week average based on date | Excel Discussion (Misc queries) |