Rolling Average Output to an Array

[bsamuels]

Hi,

I have several thousand 19-day time series and am trying to find the maximum 5-day rolling average for each series. I would like to do this in one array formula. Is this possible?

As an example, the time series below would take the Value column and output the max average of 166.67, without a need for a third column.

Day Value 5-Day moving Average
1 122.97
2 153.55
3 156.53
4 96.41
5 152.11 136.31
6 168.16 145.35
7 183.26 151.29
8 164.05 152.80
9 136.58 160.83
10 181.77 166.76
11 154.01 163.93
12 156.27 158.54
13 129.78 151.68
14 108.45 146.06
15 180.08 145.72
16 181.39 151.19
17 154.78 150.90
18 95.16 143.97
19 180.88 158.46
Max Average 166.76

[Tony Valko]

Try this...

Data Range

	A	B	C	D
1	------	------	------	------
2	122.97			166.764
3	153.55
4	156.53
5	96.41
6	152.11	136.31
7	168.16	145.35
8	183.26	151.29
9	164.05	152.8
10	136.58	160.83
11	181.77	166.76
12	154.01	163.93
13	156.27	158.54
14	129.78	151.68
15	108.45	146.06
16	180.08	145.72
17	181.39	151.19
18	154.78	150.9
19	95.16	143.97
20	180.88	158.46

This array formula** entered in D2:

=MAX(SUBTOTAL(1,OFFSET(A2,ROW(A2:A16)-ROW(A2),,5)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Note how the range in the ROW function ends 5 cells from the actual end of data.

We can probably calculate that directly into the formula if need be.

[JohnTopley]

To get the MAXIMUM of each series

in D1

=MAX(OFFSET($B$1,(ROWS($1:1)-1)*19+1,,19))

Copy down to give you the max of each 19 day series.