Moving average skipping cells that contain certain character

T

triforce

New Member
Joined
Jul 1, 2015
Messages
12
[TABLE="class: grid, width: 800"]
<tbody>[TR]
[TD]wind.size[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="align: center"]0[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]-[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]1[/TD]
[TD="align: center"]27[/TD]
[/TR]
[TR]
[TD="align: center"][/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]5[/TD]
[TD="align: center"]3[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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).

Code: 
=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) ?
 
Last edited:

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
Sort by date Sort by votes
Hello,

You could test following

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

Hope this will help
 
Upvote 0
Hi James. Thanks for the reply.

I tried
Code: 
=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?

James006 said:
Hello,

You could test following

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

Hope this will help
Click to expand...
 
Upvote 0
Hi again,

What are you trying to achieve ...?
 
Upvote 0
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
Code: 
[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.
 
Upvote 0
Hello again,

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

Maybe this:-

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

[TABLE="width: 770"]
<colgroup><col width="70" span="11" style="width:52pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 70"][/TD]
[TD="class: xl65, width: 70"]A[/TD]
[TD="class: xl65, width: 70"]B[/TD]
[TD="class: xl65, width: 70"]C[/TD]
[TD="class: xl65, width: 70"]D[/TD]
[TD="class: xl65, width: 70"]E[/TD]
[TD="class: xl65, width: 70"]F[/TD]
[TD="class: xl65, width: 70"]G[/TD]
[TD="class: xl65, width: 70"]H[/TD]
[TD="class: xl65, width: 70"]I[/TD]
[TD="class: xl65, width: 70"]J[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl66, width: 70"]wind.size[/TD]
[TD="class: xl63, width: 70"] [/TD]
[TD="class: xl63, width: 70"] [/TD]
[TD="class: xl63, width: 70"] [/TD]
[TD="class: xl63, width: 70"] [/TD]
[TD="class: xl63, width: 70"] [/TD]
[TD="class: xl63, width: 70"] [/TD]
[TD="class: xl63, width: 70"] [/TD]
[TD="class: xl63, width: 70"] [/TD]
[TD="class: xl63, width: 70"] [/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl66, width: 70"]3[/TD]
[TD="class: xl63, width: 70"]0[/TD]
[TD="class: xl63, width: 70"]-[/TD]
[TD="class: xl63, width: 70"]8[/TD]
[TD="class: xl63, width: 70"]4[/TD]
[TD="class: xl63, width: 70"]3[/TD]
[TD="class: xl63, width: 70"]-[/TD]
[TD="class: xl63, width: 70"]2[/TD]
[TD="class: xl63, width: 70"]1[/TD]
[TD="class: xl63, width: 70"]27[/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl63, width: 70"] [/TD]
[TD="class: xl64, width: 70"]4[/TD]
[TD="class: xl64, width: 70"] [/TD]
[TD="class: xl64, width: 70"]5[/TD]
[TD="class: xl64, width: 70"]3[/TD]
[TD="class: xl64, width: 70"]2[/TD]
[TD="class: xl64, width: 70"] [/TD]
[TD="class: xl64, width: 70"]10[/TD]
[TD="class: xl63, width: 70"] [/TD]
[TD="class: xl63, width: 70"] [/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
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.)

admiral100 said:
Hi,

Maybe this:-

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

[TABLE="width: 770"]
<tbody>[TR]
[TD="class: xl65, width: 70"][/TD]
[TD="class: xl65, width: 70"]A[/TD]
[TD="class: xl65, width: 70"]B[/TD]
[TD="class: xl65, width: 70"]C[/TD]
[TD="class: xl65, width: 70"]D[/TD]
[TD="class: xl65, width: 70"]E[/TD]
[TD="class: xl65, width: 70"]F[/TD]
[TD="class: xl65, width: 70"]G[/TD]
[TD="class: xl65, width: 70"]H[/TD]
[TD="class: xl65, width: 70"]I[/TD]
[TD="class: xl65, width: 70"]J[/TD]
[/TR]
[TR]
[TD="class: xl65"]1[/TD]
[TD="class: xl66, width: 70"]wind.size[/TD]
[TD="class: xl63, width: 70"][/TD]
[TD="class: xl63, width: 70"][/TD]
[TD="class: xl63, width: 70"][/TD]
[TD="class: xl63, width: 70"][/TD]
[TD="class: xl63, width: 70"][/TD]
[TD="class: xl63, width: 70"][/TD]
[TD="class: xl63, width: 70"][/TD]
[TD="class: xl63, width: 70"][/TD]
[TD="class: xl63, width: 70"][/TD]
[/TR]
[TR]
[TD="class: xl65"]2[/TD]
[TD="class: xl66, width: 70"]3[/TD]
[TD="class: xl63, width: 70"]0[/TD]
[TD="class: xl63, width: 70"]-[/TD]
[TD="class: xl63, width: 70"]8[/TD]
[TD="class: xl63, width: 70"]4[/TD]
[TD="class: xl63, width: 70"]3[/TD]
[TD="class: xl63, width: 70"]-[/TD]
[TD="class: xl63, width: 70"]2[/TD]
[TD="class: xl63, width: 70"]1[/TD]
[TD="class: xl63, width: 70"]27[/TD]
[/TR]
[TR]
[TD="class: xl65"]3[/TD]
[TD="class: xl63, width: 70"][/TD]
[TD="class: xl64, width: 70"]4[/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"]5[/TD]
[TD="class: xl64, width: 70"]3[/TD]
[TD="class: xl64, width: 70"]2[/TD]
[TD="class: xl64, width: 70"][/TD]
[TD="class: xl64, width: 70"]10[/TD]
[TD="class: xl63, width: 70"][/TD]
[TD="class: xl63, width: 70"][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
Ouch, there is a problem with the formula.

[TABLE="class: grid, width: 770"]
<tbody>[TR]
[TD="width: 70"][/TD]
[TD="width: 70"]A[/TD]
[TD="width: 70"]B[/TD]
[TD="width: 70"]C[/TD]
[TD="width: 70"]D[/TD]
[TD="width: 70"]E[/TD]
[TD="width: 70"]F[/TD]
[TD="width: 70"]G[/TD]
[TD="width: 70"]H[/TD]
[TD="width: 70"]I[/TD]
[TD="width: 70"]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD="width: 70"]wind.size[/TD]
[TD="width: 70"][/TD]
[TD="width: 70"][/TD]
[TD="width: 70"][/TD]
[TD="width: 70"][/TD]
[TD="width: 70"][/TD]
[TD="width: 70"][/TD]
[TD="width: 70"][/TD]
[TD="width: 70"][/TD]
[TD="width: 70"][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD="width: 70"]3[/TD]
[TD="width: 70"]0[/TD]
[TD="width: 70"]-[/TD]
[TD="width: 70"]8[/TD]
[TD="width: 70"]-[/TD]
[TD="width: 70"]-[/TD]
[TD="width: 70"]-[/TD]
[TD="width: 70"]2[/TD]
[TD="width: 70"]1[/TD]
[TD="width: 70"]27[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD="width: 70"][/TD]
[TD="width: 70"]4[/TD]
[TD="width: 70"]
[/TD]
[TD="width: 70"]5[/TD]
[TD="width: 70"]
[/TD]
[TD="width: 70"]
[/TD]
[TD="width: 70"]
[/TD]
[TD="width: 70"]10[/TD]
[TD="width: 70"][/TD]
[TD="width: 70"][/TD]
[/TR]
</tbody>[/TABLE]

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.
 
Upvote 0
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),"")))


[TABLE="width: 791"]
<colgroup><col span="2"><col><col span="8"></colgroup><tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]wind.size[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]3[/TD]
[TD]0[/TD]
[TD]-[/TD]
[TD]8[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]-[/TD]
[TD]2[/TD]
[TD]1[/TD]
[TD]27[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] [/TD]
[TD]3.33333333[/TD]
[TD] [/TD]
[TD]3.666667[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD]10[/TD]
[TD]14[/TD]
[TD]27[/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
You must log in or register to reply here.

Similar threads

T
  • Question Question
Moving average last 5 numeric number, N/A for any zero cells and the omitting that for next moving average
Replies
3
Views
299
Cubist
Cubist
R
  • Question Question
Move row to bottom of range is cell value is zero
Replies
6
Views
270
Peter_SSs
Peter_SSs
N
  • Solved
Cumulative Moving Average Help
Replies
4
Views
403
nparrillo
N
B
  • Question Question
Chart help - rainfall data
Replies
2
Views
205
JV0710
J
B
  • Solved
Average of last X transactions by customer
Replies
5
Views
311
Dave Patton
D

Forum statistics

Threads
1,223,228
Messages
6,170,875
Members
452,363
Latest member
merico17

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top