Moving Average

[Steve 1962]

Hi All

I have a formula in column H that seems to be incorrect. I need it to give me a moving average based on column F. I need to be able to adjust the moving average from a value set in cell A1 (could be 5 week moving average or as in this case, 8 week moving average). Obviously the first output based on 8 week moving average would show in cell H9.

Also, is it possible to change from an moving AVERAGE to a moving MEDIAN ?

Thanks

8	Week	Zero	Lower	Upper	Section A	Section B	Moving AV
	21	0	30	30	9	107	#VALUE!
	22	0	30	30	1	77	#VALUE!
	23	0	30	30	9	90	#VALUE!
	24	0	30	30	8	36	#VALUE!
	25	0	30	30	20	42	#VALUE!
	26	0	30	30	17	120	#VALUE!
	27	0	30	30	9	49	#VALUE!
	28	0	30	30	5	14	#VALUE!	??
	29	0	30	30	-16	55	#VALUE!
	30	0	30	30	5	114	#VALUE!
	31	0	30	30	-19	51	#VALUE!
	32	0	30	30	-3	118	#VALUE!
	33	0	30	30	4	53	#VALUE!
	34	0	30	30	-9	39	#VALUE!
	35	0	30	30	26	78	#VALUE!
	36	0	30	30	6	36	#VALUE!
	37	0	30	30	3	82	#VALUE!
	38	0	30	30	-16	82	#VALUE!
	39	0	30	30	33	119	#VALUE!
	40	0	30	30	-10	45	#VALUE!
	41	0	30	30	-5	103	#VALUE!
	42	0	30	30	-41	82	#VALUE!
	43	0	30	30	27	87	#VALUE!

 

[JoeMo]

Maybe:

Book1.xlsm
	A	B	C	D	E	F	G	H
1	8	Week	Zero	Lower	Upper	Section A	Section B	Moving AV
2		21	0	30	30	9.5	107.5
3		22	0	30	30	1	77
4		23	0	30	30	8.5	90
5		24	0	30	30	8	36
6		25	0	30	30	20.5	42.5
7		26	0	30	30	17	120
8		27	0	30	30	9	49
9		28	0	30	30	5	14	9.8125
10		29	0	30	30	-15.5	55	6.6875
11		30	0	30	30	5	114	7.1875
12		31	0	30	30	-18.5	51	3.8125
13		32	0	30	30	-3	117.5	2.4375
14		33	0	30	30	4.5	53.5	0.4375
15		34	0	30	30	-9	39.5	-2.8125
16		35	0	30	30	26.5	78.5	-0.625
17		36	0	30	30	6	36.5	-0.5
18		37	0	30	30	3	82	1.8125
19		38	0	30	30	-16	82	-0.8125
20		39	0	30	30	33.5	118.5	5.6875
21		40	0	30	30	-9.5	45	4.875
22		41	0	30	30	-5	103	3.6875
23		42	0	30	30	-41	82	-0.3125
24		43	0	30	30	27	87	-0.25
Sheet7

Cell Formulas
Range	Formula
H2:H24	H2	=IF(B2<28,"",AVERAGE(OFFSET(F2,-7,0,8)))

 

[Steve 1962]

Hey thanks JoeMo -

Just a comment -

The value in cell B2 (in this case 21) may change. If so, how do I stil show blanks in column H ? The value of 28 in the formula would not apply.

Thanks

 

[JoeMo]

Hey thanks JoeMo -

Just a comment -

The value in cell B2 (in this case 21) may change. If so, how do I stil show blanks in column H ? The value of 28 in the formula would not apply.

Thanks

You are welcome - thanks for the reply. The value in B2 should be less than the value in col B for the 8th week (in the example case that's 28).

 

[Steve 1962]

You are welcome - thanks for the reply. The value in B2 should be less than the value in col B for the 8th week (in the example case that's 28).

Sorry JoeMo, my mistake - I asked the wrong question with the last reply. I should have said that the value in B2 may commence at another week such as 7 or 16 or other (and not 21). Is so, that would make the hard-coded value of 28 in the formula incorrect. Instead of being set at 28, it needs to be dynamic based on what is the commencing week number (in cell B2).

Thanks

 

[JoeMo]

Sorry JoeMo, my mistake - I asked the wrong question with the last reply. I should have said that the value in B2 may commence at another week such as 7 or 16 or other (and not 21). Is so, that would make the hard-coded value of 28 in the formula incorrect. Instead of being set at 28, it needs to be dynamic based on what is the commencing week number (in cell B2).

Thanks

You are welcome. To make it dynamic you can use this formula in H2 (where A1 holds the number of weeks you want to include in the moving average):

=IF(B2<B$2+A$1-1,"",AVERAGE(OFFSET(F2,-A$1+1,0,A$1)))