Thread: Moving Average for variable range? (2002 SP-2)

1. Moving Average for variable range? (2002 SP-2)

I was curious how easy/difficult it would be to figure out the moving average of the strength of the aftershocks for this morning's earthquakes during a given half-hour window. I massaged the data into Excel and looked at the Moving Average features (Tools>Data Analysis...), but it takes only a fixed number of cells. I looked at the DAVERAGE database feature but could not get any valid results. Maybe this just isn't possible without some code.

Anyone want to take a crack at this? I've attached the data. There is a column in italics which shows the time 30 minutes prior to the recorded time of the aftershock. I thought this might be useful as input for a database function, as it could be used to determine the lower bound of data to average. Feel free to throw it away if it isn't.

2. Re: Moving Average for variable range? (2002 SP-2)

I used two intermediary columns for clarity:
The moving sum =SUM((\$B\$6:\$B\$214>=B6-\$J\$1)*(\$B\$6:\$B\$214<=B6)*\$A\$6:\$A\$214) entered as an array formula (confirm with Ctrl+Shift+Enter) in G6 and filled down.
The moving count =SUM((\$B\$6:\$B\$214>=B6-\$J\$1)*(\$B\$6:\$B\$214<=B6)) entered as an array formula in H6 and filled down.
Cell I6 contains the moving average =H6/G6, also filled down.
I added I6:I214 to the chart as a new series.
See attached (zipped, the .xls is over 100 KB)