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

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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

montecarlo2012
  • Question Question
change display from horizontal to vertical
Replies
2
Views
100
montecarlo2012
montecarlo2012
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
327
Cubist
Cubist
D
  • Question Question
Possible Use of CountIF and AVERAGE Functions
Replies
1
Views
130
Denny57
D
D
  • Question Question
VBA Code to Apply an AVERAGE of a Range on Cells as New Records are added Via a User Form
Replies
2
Views
72
Denny57
D
R
  • Question Question
Move row to bottom of range is cell value is zero
Replies
6
Views
297
Peter_SSs
Peter_SSs

Forum statistics

Threads
1,223,903
Messages
6,175,287
Members
452,631
Latest member
a_potato

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