Moving Average

[cunningAce]

Hi All,

Looking for a little help with the following please,

I want to find the average according to the variable in the "Average Weeks" cell.

So in this example it would look at the last 3 weeks figures, Weeks 4 - 6 and work out the average = 9243

As in the example, the future cells Week 7, 8 9 10.... will contain zeros, and I don't want the average to be affected by this.

Thanks in advance for your help

Book1
	A	B	C	D	E	F	G	H	I	J
1
2	Week 1	Week 2	Week 3	Week 4	Week 5	Week 6	Week 7	Week 8	Week 9	Week 10
3	8997	12047	10645	10533	10195	7000	0	0	0	0
4
5	Average Weeks	3
6	Average	9243
7
Sheet1

 

[Aladin Akyurek]

In C6 control+shift+enter, not just enter:

=AVERAGE(OFFSET(INDEX(B3:K3,MATCH(9.99999999999999E+307,1/(B3:K3))),0,0,1,-3))

 

[cunningAce]

Thanks for this, Seems to work well.

Would you mind explaining what this part of the formula is doing?

MATCH(9.99999999999999E+307,1/(B3:K3)​

 

[Aladin Akyurek]

Thanks for this, Seems to work well.

Would you mind explaining what this part of the formula is doing?

MATCH(9.99999999999999E+307,1/(B3:K3)​

1. MATCH ignore error values when they occur among non-error values: For example, in #N/A,1,0.2,#DIV/0! the errors are skipped.

2. MATCH (and kindred functions like LOOKUP, etc.) land on the last numeric value in a range when they are given 9.99999999999999E+307 as a look up value. This big number is an Excel constant, indicating a limit value.

3. The expression 1/(B3:K3) evaluates to decimal values for non-zero nimbers and #DIV/0! errors for 0 values, etc., from which MATCH picks out the position of the last decimal value and returns the correlated value fom B3:K3. INDEX gives the address of that value to the OFFSET function for further processing.

Hope this helps.

 

[cunningAce]

Thanks for the explanation.
Helped a lot.

 

[Aladin Akyurek]

Thanks for the explanation.
Helped a lot.

You are welcome.