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!

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

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! =)

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! =)