Results 1 to 3 of 3
  1. #1
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    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. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    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)

  3. #3
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

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

    Amazing. I have so much to learn about this program. Thanks, Hans!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •