how do I make a single formula that will take the n-week average of m-week averages?
Suppose I have data in A2:A21
, and in D21
, I want the 8 week average of 4-week moving averages up to A21. So my idea would be
{=AVERAGE(AVERAGE(OFFSET(A21,-4+1-ROW(OFFSET(A1,0,0,8))+1,0,4)))}
So for example, with this data:
data 4 wk aver 8 wk avg avg
79
79
66
38 66
34 54
13 38
22 27
50 30
40 31
98 53
81 67 46
27 62 45
64 68 47
12 46 48
83 47 50
94 63 54
4 48 57
41 56 57
4 36 53
6 14 47
I am looking for a formula to calculate 47... My formula above calculates 57.8.
Answer is to use SUBTOTAL()
on the inner average instead. This will return a column of averages, so the outer AVERAGE()
will have the correct behavior.
{=AVERAGE(SUBTOTAL(1,OFFSET(A21,-4+1-ROW(OFFSET(A1,0,0,8))+1,0,4)))}
The reason the original solution didn't work is because the inner AVERAGE
was just taking the total average of all the arrays, instead of returning a column of their individual averages. I find this SUBTOTAL()
trick useful when you need to do this kind of thing.