Moving average

[bobdobalina]

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.

Help!!!! Thanks in advance.

[darkyam]

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

[bobdobalina]

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

[darkyam]

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

[bobdobalina]

Brilliant. Works perfectly. Thanks!

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

[darkyam]

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.

[DonkeyOte]

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.

[darkyam]

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.

[bobdobalina]

both work. thank you.