OFFSET Trouble

[Steve 1962]

Hi

Can't get the OFFSET function to work correctly.

In A2, I want to nominate the amount of moving weeks to use for the average. In this example, I have selected 3 however, weeks 1 & 2 are still showing in cells D4 & D5. With the IFERROR function, they should be blank.

If I was to select 9 in cell A2, then I would expect all cells from D4 to D11 to be blank also.

Thanks

Book1
	A	B	C	D
1	Weeks		x
2	3		x
3		Week	x	Moving AV
4		1	10.3	10.4
5		2	9.7	10.4
6		3	11.1	11.1
7		4		10.6
8		5		10.3
9		6	10.6	10.6
10		7	10.1	10.5
11		8	11.1	10.4
12		9	10.5	10.3
13		10	9.5	10.4
Weekly AV (2)

Cell Formulas
Range	Formula
D4:D13	D4	=IFERROR(AVERAGE(OFFSET($C$3,ROW()-$A$2,0,$A$2,1)),"")

 

[Takae]

Hope this helps.
=IF(B4<>$A$2,"",AVERAGE(OFFSET($C$4,0,0,$A$2,1)))

 

[maabadi]

With your formula you want average of 3 weeks down from that cell if week number equal or greater than A2
I think you want this:

=IF(B4<$A$2,"",AVERAGE(OFFSET($C$4,0,0,$A$2,1)))

 

[Steve 1962]

If I place a 1 in cell A2, each of the values in column D should mirror those in column C. They should be the same as it is only a 1 x week moving average. Don't seem to be getting that answer.

 

[maabadi]

Try this:

=IFERROR(IF(B4<$A$2,"",AVERAGE(OFFSET($C$3,ROWS($D$3:D3),0,$A$2,1))),"")

 

[Steve 1962]

Thanks again. Just an observation -

If I select 5 in cell A2, I should be getting the last 5 x week rolling average. If I sample weeks 11 to 15 (over 5 x weeks) in cells C14 to C18, I should get an average of 10.5 in cell D18. The output in cell D18 is 10.3.

Thanks

Book1
	A	B	C	D
1	Weeks		x
2	5		x
3		Week	YUB>HGD	Moving AV
4		1	10.3
5		2	9.7
6		3	11.1
7		4
8		5		10.6
9		6	10.6	10.3
10		7	10.1	10.4
11		8	11.1	10.5
12		9	10.5	10.2
13		10	9.5	10.1
14		11	10.8	10.5
15		12	10.8	10.5
16		13	9.3	10.3
17		14	10.2	10.5
18		15	11.3	10.3
Weekly AV (4)

Cell Formulas
Range	Formula
D4:D18	D4	=IFERROR(IF(B4<$A$2,"",AVERAGE(OFFSET($C$3,ROWS($D$3:D3),0,$A$2,1))),"")

 

[maabadi]

I tell you Before you see your result at first cell at range

With your formula you want average of 3 weeks down from that cell if week number equal or greater than A2

then you see result of that at D14 not D18.
For that Use this:

=IFERROR(IF(B4<$A$2,"",AVERAGE(OFFSET($C$3,ROWS($D$3:D3)-$A$2+1,0,$A$2,1))),"")

 

[Steve 1962]

Absolutely PERFECT !!! Thanks Maabadi for your help and patience.

Have a good week.

 

[maabadi]

You're Welcome & thanks for feedback.