# Time-Series Moving Average

1. ## Time-Series Moving Average

Hi there,

I have a set of time-series data that I need to analyse. It consists of a whole year of data (365 days). However, each day is broken up into half-hour interval data points (48 data points/ day). I would like to run a 5-day average using Excel. However, whenever I average the values for one day and subsequently drag it down for the entire time series, the moving average merely moves one cell down, and not one whole day down (jumping 48 cells to the following day). I hope this makes sense! I have attached a screenshot showing part of a single-year dataset if it makes things clearer.

Any help at all is greatly appreciated!

2. ## Re: Time-Series Moving Average

Try

``Please Login or Register  to view this content.``
where G3 is day number (so 1 or 2 or 50)

3. ## Re: Time-Series Moving Average

Hi Evolta, I just tried that and unfortunately that didn't work. Thanks so much for your reply though. Any other ideas?

4. ## Re: Time-Series Moving Average

I have other ideas, give me a minute

5. ## Re: Time-Series Moving Average

Solution_2.xlsx

Many minutes have passed, got a phonecall that had to be given priority.

6. ## Re: Time-Series Moving Average

Your formula and solution looks fantastic! I haven't yet applied it to my data set but it looks as though it would work great! I will try it in a moment and will let you know how it goes. Thank you so very much for your help - I've been driving myself crazy the past month! =)

7. ## Re: Time-Series Moving Average

Hi Evolta,

I just applied the formula to my dataset and it works perfectly - thank you so much!

I just have one more question - a bit more simple, but similarly something I have been struggling with. So, with reference to that same set of data, if all I want is to simply quickly get the average for EACH day, how should the formula be like? I have 10 years worth of data so having to drag and individually select each day (48 data points) for a whole 10 years can be very taxing!

Thank you from afar! =)

8. ## Re: Time-Series Moving Average

A Pivot Table should do both in a minute

9. ## Re: Time-Series Moving Average

That should not be difficult to do.

I don't know how you want the layout to be. You can make a 366 rows * 10 columns table. And just change the formula a bit to look-up the correct values from different sheets.
Or you can make a selection cell, and calculate the rest from there.
Or you can write a macro and apply it to a pink button.

You could do a lot... its really a matter of what you want.

10. ## Re: Time-Series Moving Average

Something like this?
Solution_3.xlsx

Change the code for: (I had it still set for 5 day avarage)
``Please Login or Register  to view this content.``

If you wish something else just let me know.
If the question is solved, please mark it as such.
If people helped you, don't hesitate to give them reputation.

There are currently 1 users browsing this thread. (0 members and 1 guests)