Moving average skipping cells that contain certain character

[triforce]

wind.size
3	0	-	8	4	3	-	2	1	27
	4		5	3	2		10

<tbody>
</tbody>

1) I want to create a moving average (going to the right) with a defined window size (A2).
2) I don't want to return a number if the character above is a dash.
3) For the moving average I want to skip cells that contain a dash above (i.e. moving average of non-dash cells).

2) C3 doesn't return anything because the character above is a dash.
3) B3 returns 4 because (B2+D2+E2)/A2 = (0+8+4)/3 = 4.

I have found a solution for 1) and 2).

=IF(B2<>"-",AVERAGE(OFFSET(A2,0,1,1,$A2)),"")

IF(B2<>"-" solves 1), and AVERAGE(OFFSET(A2,0,1,1,$A2)) solves 2).

Does anyone have a solution for 3) ?

 

[James006]

Hello,

You could test following

=AVERAGEIF(A2:J2,"<>"&"-")

Hope this will help

 

[triforce]

Hi James. Thanks for the reply.

I tried

=AVERAGEIF(A2:J2,"<>"&"-"(OFFSET(A2,0,1,1,$A2)))

but Excel complains about the function when I try to press enter. Do you know how to incorporate it into my formula?

Hello,

You could test following

=AVERAGEIF(A2:J2,"<>"&"-")

Hope this will help

 

[James006]

Hi again,

What are you trying to achieve ...?

 

[triforce]

I want a moving average in the forward direction, with a defined size (here set to 3), so that:
B3 averages B2,C2,D2
C3 averages C2,D2,E2
D3 averages D2,E2,F2
etc.

This is what I have achieved with

[COLOR=#333333]AVERAGE(OFFSET(A2,0,1,1,$A2))[/COLOR]

What I have problem with, is skipping cells that are just a dash character.

In the table above, I want B3 to average B2,D2,E2, because C2 is a dash.

 

[James006]

Hello again,

Have you tested the AverageIf() formula to determine if it returns ... or not ... your expected result ...???

 

[admiral100]

Hi,

Maybe this:-

B3 =IF(B2="-","",AVERAGE(OFFSET(B1,1,,,$A$2+COUNTIF(B2:D2,"=-"))))

	A	B	C	D	E	F	G	H	I	J
1	wind.size
2	3	0	-	8	4	3	-	2	1	27
3		4		5	3	2		10

<colgroup><col width="70" span="11" style="width:52pt"> </colgroup><tbody>
</tbody>

 

[triforce]

That works perfect!

Just a small extra, do you happen to know how to make it not show anything in the last two cells? Right now it shows 14 in I3 (1+27)/3, and 27 in J3. The problem is that they don't meet the criteria of the defined size of the moving average (here set to 3 cells.)

Hi,

Maybe this:-

B3 =IF(B2="-","",AVERAGE(OFFSET(B1,1,,,$A$2+COUNTIF(B2:D2,"=-"))))

	A	B	C	D	E	F	G	H	I	J
1	wind.size
2	3	0	-	8	4	3	-	2	1	27
3		4		5	3	2		10

<tbody>
</tbody>

 

[triforce]

Ouch, there is a problem with the formula.

	A	B	C	D	E	F	G	H	I	J
1	wind.size
2	3	0	-	8	-	-	-	2	1	27
3		4		5				10

<tbody>
</tbody>

D3 outputs 5, even though (8+2+1)/3 = 3.667.
So it doesn't seem to handle multiple cells with a dash.
The preferred way it that it just scans until it finds 3 cells with a number, regardless of the gap size.

I also noticed the B3:D3 in the formula, which is 3 cells (the window size here). But what if I change the window size to something else? The preferred way is that I can dynamically change the window size in A2.

 

[admiral100]

Hi ,

Try this version:-

Ctrl+Shift+Enter Not just Enter

B3 =IF(B2="-","",AVERAGE(IFERROR(LOOKUP(SMALL(((IF(B$2:$J2 <> "-",COLUMN(B2:$J$5)))),ROW($A$1:INDIRECT("A"&$A$2))),COLUMN(B2:$J$5),B$2:$J2),"")))

	A	B	C	D	E	F	G	H	I	J
1	wind.size
2	3	0	-	8	-	-	-	2	1	27
3		3.33333333		3.666667				10	14	27

<colgroup><col span="2"><col><col span="8"></colgroup><tbody>
</tbody>