More elegant moving average

aweis

New Member
Joined
Nov 15, 2009
Messages
1
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.
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,178
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

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,178
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))))
 

Watch MrExcel Video

Forum statistics

Threads
1,100,211
Messages
5,473,195
Members
406,851
Latest member
Debbren

This Week's Hot Topics

Top