Moving Average with Missing Data

terapin

I'm trying to calculate a moving average of some data that has some holes in it.  I can't seem to figure out how to caculate a moving average that only includes non missing data and that only calculates the result for rows with non missing data.  Any suggestions would be appreciated.

 

New Table( "Untitled 5",
	Add Rows( 30 ),
	Compress File When Saved( 1 ),
	New Table Variable( "Moving Avg Window", 5 ),
	New Column( "DateTime",
		Numeric,
		"Continuous",
		Format( "m/d/y h:m", 19 ),
		Input Format( "m/d/y h:m" ),
		Set Values(
			[3225888000, 3225888900, 3225889800, 3225890700, 3225891600, 3225892500, 3225893400, 3225894300,
			3225895200, 3225896100, 3225897000, 3225897900, 3225898800, 3225899700, 3225900600, 3225901500,
			3225902400, 3225903300, 3225904200, 3225905100, 3225906000, 3225906900, 3225907800, 3225908700,
			3225909600, 3225910500, 3225911400, 3225912300, 3225913200, 3225914100]
		)
	),
	New Column( "data",
		Numeric,
		"Continuous",
		Format( "Best", Use thousands separator( 0 ), 15 ),
		Set Values( [5, 5, 5, 5, 5, ., 5, 5, 5, 5, 5, 5, 5, 5, ., 5, 5, 5, 5, 5, 5, 5, ., ., 5, 5, 5, 5, 5, 5] )
	),
	New Column( "expected run avg",
		Numeric,
		"Continuous",
		Format( "Best", 12 ),
		Set Values( [., ., ., ., 5, ., 5, 5, 5, 5, 5, 5, 5, 5, ., 5, 5, 5, 5, 5, 5, 5, ., ., 5, 5, 5, 5, 5, 5] )
	),
	New Column( "run avg",
		Numeric,
		"Continuous",
		Format( "Fixed Dec", 12, 1 ),
		Formula(
			If( Row() < :Moving Avg Window,
				.,
				Sum( :data[Index( Row() - (:Moving Avg Window - 1), Row() )] ) / :Moving Avg Window
			)
		)
	)
);

ms

The Mean() function neglects any missing values. The below formula stick to the time window but do not include missing values when calculating the mean.

If(Row() < :Moving Avg Window,
    Mean(:data[Index(1, Row())]),
    Mean(:data[Index(Row() - (:Moving Avg Window - 1), Row())])
);