More elegant moving average

[aweis]

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.

I've also tried the following, which did not work: =average(offset(g6,0,match(9.99999999999e+307,g6:fc6)-30,0,30))

Help!!!! Thanks in advance.

 

[Aladin Akyurek]

Define BigNum using Insert|Name|Define as referring to:

=9.99999999999999E+307

Then try...

=AVERAGE(OFFSET(G6,0,0,1,MIN(30,MATCH(BigNum,G6:FC6))))

 

[Aladin Akyurek]

Define BigNum using Insert|Name|Define as referring to:

=9.99999999999999E+307

Then try...

=AVERAGE(OFFSET(G6,0,0,1,MIN(30,MATCH(BigNum,G6:FC6))))

I think you actually ask for:

=AVERAGE(OFFSET(INDEX(G6:FC6,MATCH(BigNum,G6:FC6)),0,0,1,-MIN(30,MATCH(BigNum,G6:FC6))))