Moving average calculation over a date range
- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Mute
- Printer Friendly Page
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Trying to perform a moving average over a range of dates for a specific value. In the example data set attached, the range between Finish and Start Date-Time is 15 days. Step one involves a moving average using one of the value columns (i.e. Value1) using values in rows between the Finish and the Start dates. You will see there is not a consistent number of records to average over by date. This step I can't quite figure out. The output must calculate back from the Finish Date over the last 15 days. I.e. For row 1, the average is equal to just the value for that day. Once there are over 15 days, use data only within the 15 day window.
StartDate is not required as the 15 could be in a formula. Any hints?
Step two requires I then perform across all rows (Value2, Value3) but that can wait till I figure out step 1.
Thanks
Solved! Go to Solution.
- Labels:
- Developer
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
There are a few ways to achieve the desired result here, I choose to use a Batch Macro. What this macro does is for each day (control parameter), it matches all the relevant date records (last 15 days), and averages all the values during that time period.
Check out the attached solution and let me know if this works for you.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
I tend to do this a slightly different way to @CharlieS.
First make a list of all the dates (value dates, start and end dates).
Next create running totals including a count column
Then join to start and end dates to make averages.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Charlie,
Your batch macro was spot on and solve the challenging problem of how to accurately calculate averages. I worked through each calculation to assure indeed it was correct.
Thanks
Steve
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Your solution had the same problem I struggled to accurately calculate averages over a group. It is not simply taking averages of averages. If one changes the 'Count = 1' to a summarized count of the number of entries for the day, your solution is closer but still not accurate. Here is a link to a more rigorous explanation of why an average of averages is not correct. I did learn one technique from your solution - so thanks.
Steve
https://math.stackexchange.com/questions/95909/why-is-an-average-of-an-average-usually-incorrect
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Thanks for this post. It helped me figure out what I needed to do.
For reference if you are looking to average over a set number of records, then you can solve this with just one tool - the 'multi-row formula' tool.
In my case, I was looking for average of previous 13 rows of records, so I just used the Average function in the "Expression' box of that tool across 13 rows, like this:
Average([Row-12:Value],[Row-11:Value],[Row-10:Value],[Row-9:Value],
[Row-8:Value],[Row-7:Value],[Row-6:Value],[Row-5:Value],
[Row-4:Value],[Row-3:Value],[Row-2:Value],[Row-1:Value],[Value])
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Will this work for a 3 hour rolling average.
- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Notify Moderator
Yes, it will but you'll need to adjust your moving average to work on a timestamp instead of just a date.