# Moving average

1. ## Moving average

I'm not an advanced excel user, but have figured out an layman's solution to my first problem. The issue is the second problem.

I'm looking for a 30 day moving average that automatically updates based on newly entered data. I've successfully used the following formula to return a 7-day moving average:

=AVERAGE((OFFSET(G6,0,MATCH(MAX(G6:FC6)+1,G6:FC6,1)-1)),(OFFSET(G6,0,MATCH(MAX(G6:FC6)+1,G6:FC6,1)-2)),(OFFSET(G6,0,MATCH(MAX(G6:FC6)+1,G6:FC6,1)-3)),(OFFSET(G6,0,MATCH(MAX(G6:FC6)+1,G6:FC6,1)-4)),(OFFSET(G6,0,MATCH(MAX(G6:FC6)+1,G6:FC6,1)-5)),(OFFSET(G6,0,MATCH(MAX(G6:FC6)+1,G6:FC6,1)-6)),(OFFSET(G6,0,MATCH(MAX(G6:FC6)+1,G6:FC6,1)-7)))

The obvious problem is that this isn't scalable for a 30 day. I'm positive there is a more elegant solution out there, I just can't find it or execute on any of the formula's I've found.

Row 1 has dates by day.
Rows 2 - 70 have daily updated data, whereby each row needs its own 30-day moving average to compare against the 7-day moving average.
The data needs to be in Rows, not columns, to hook into other legacy reporting, so transposing isn't possible.
It needs to scale for up to 6 months of data to be averaged each day on a 30-day rolling basis.

2. ## Re: More elegant moving average

=average(offset(g6,0,match(9.99999999999e+307,g6:fc6)-30,0,30))

3. ## Re: More elegant moving average

Thanks for the response darkyam. I put this in the appropriate cell and get a "#REF!". Am I doing something wrong?

4. ## Re: More elegant moving average

No, this was my fault. Replace the second 0 with 1. Sorry.

5. ## Re: More elegant moving average

Brilliant. Works perfectly. Thanks!

What does the 9.9999...E+307 function return? I'm not familiar.

6. ## Re: More elegant moving average

That's the largest value Excel can handle (may be a few more 9's in there, but it shouldn't really matter). Unless your data actually has that value, Excel will just try to keep matching until the end of a reference and return the position of the last cell in an array with an entry.

7. ## Re: Moving average

If I've understood you can use INDEX rather than OFFSET to calculate the same but in non-volatile fashion (reduce calc overheads) eg:

``Please Login or Register  to view this content.``
The above also utilises a COUNT so as to ensure the Average will be returned if less than 30 days data exists.

As with prior formulae we're assuming a contiguous data set.

8. ## Re: Moving average

Somehow, DonkeyOte, I knew you'd post a reply with an Index solution.
I had tried to get it to work that way first, but had a brain cramp and couldn't do it.

9. ## Re: Moving average

both work. thank you.

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