Moving Average

[cunningAce]

Hi,

Hoping someone could help me with the following please,

I am looking to find the average of a range of numbers (determined by the variable in the 'weeks average' cell)

So in the below examples, I want the average of the last 5 weeks for each set of numbers.
I want to ignore any zeros after the last weeks figures (week 8 in these examples) but include any zeros within the 5 week average,

Eg,
The first line would calculate an average for weeks 4-8 - (0,10,50,60,100 = 44)
The 3rd line would also calculate weeks 4-8 ignoring anything before, and any zeros after week 8. (5,10,50,60,200 = 65)

Hope this makes sense,


Thanks in advance,

	B	C	D	E	F	G	H	I	J	K	L
3	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Average
4	0	0	0	0	10	50	60	100	0	0	44
5	0	0	0	5	10	50	60	100	0	0	45
6	2	4	6	5	10	50	60	200	0	0	65
7
8
9	Weeks Average	5

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

<tbody>
</tbody>

 

[cunningAce]

Trying to get this to work, but getting a #Name error?

 

[Scott T]

Book1
	B	C	D	E	F	G	H	I	J	K	L
3	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10	Average
4	0	0	0	0	10	50	60	100	0	0	44
5	0	0	0	5	10	50	60	100	0	0	45
6	2	4	6	5	10	50	60	200	0	0	65
7
8
9	Weeks Average	5
Sheet2

Cell Formulas
Range	Formula
L4	{=AVERAGE(OFFSET(B4:INDEX(B4:K4,LARGE(IF(B4:K4<>0,COLUMN(B4:K4)-COLUMN(B4)+1),1)),0,LARGE(IF(B4:K4<>0,COLUMN(B4:K4)-COLUMN(B4)+1),1)-$C$9,1,$C$9))}
L5	{=AVERAGE(OFFSET(B5:INDEX(B5:K5,LARGE(IF(B5:K5<>0,COLUMN(B5:K5)-COLUMN(B5)+1),1)),0,LARGE(IF(B5:K5<>0,COLUMN(B5:K5)-COLUMN(B5)+1),1)-$C$9,1,$C$9))}
L6	{=AVERAGE(OFFSET(B6:INDEX(B6:K6,LARGE(IF(B6:K6<>0,COLUMN(B6:K6)-COLUMN(B6)+1),1)),0,LARGE(IF(B6:K6<>0,COLUMN(B6:K6)-COLUMN(B6)+1),1)-$C$9,1,$C$9))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[XOR LX]

How about (with CSE):

=AVERAGE(INDEX(B4:K4,N(IF(1,MATCH(1,0/B4:K4)-{0,1,2,3,4}))))

Regards

 

[XOR LX]

Ignore that. Doesn't work.

 

[Marcelo Branco]

Maybe...

	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​
1​
2​
3​	Week 1​	Week 2​	Week 3​	Week 4​	Week 5​	Week 6​	Week 7​	Week 8​	Week 9​	Week 10​	Average​
4​	0​	0​	0​	0​	10​	50​	60​	100​	0​	0​	44​
5​	0​	0​	0​	5​	10​	50​	60​	100​	0​	0​	45​
6​	2​	4​	6​	5​	10​	50​	60​	200​	0​	0​	65​
7​
8​
9​	Weeks Average​	5​

Array formula in L4 copied down
=AVERAGE(OFFSET(INDEX(B4:K4,MATCH(9.99E+307,IF(B4:K4>0,B4:K4,""))),,,,-C$9))
Ctrl+Shift+Enter

M.

 

[Marcelo Branco]

To avoid #REF errors, when the relative column of the last number > 0 is less than C9, try this new version

	B​	C​	D​	E​	F​	G​	H​	I​	J​	K​	L​
3​	Week 1​	Week 2​	Week 3​	Week 4​	Week 5​	Week 6​	Week 7​	Week 8​	Week 9​	Week 10​	Average​
4​	0​	0​	0​	0​	10​	50​	60​	100​	0​	80​	58​
5​	0​	0​	0​	5​	10​	50​	60​	100​	0​	0​	45​
6​	2​	4​	6​	5​	10​	50​	60​	200​	60​	0​	76​
7​	2​	0​	7​	0​	0​	0​	0​	0​	0​	0​	3​
8​
9​	Weeks Average​	5​

Array formula in L4 copied down
=AVERAGE(OFFSET(INDEX(B4:K4,MATCH(9.99E+307,IF(B4:K4>0,B4:K4,""))),,,,MAX(-MATCH(9.99E+307,IF(B4:K4>0,B4:K4,"")),-C$9)))
Ctrl+Shift+Enter

M.

 

[cunningAce]

Works great Marcelo, Thank you

 

[Marcelo Branco]

You're welcome. Thanks for the feedback.

M.