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.
=average(offset(g6,0,match(9.99999999999e+307,g6:fc6)-30,0,30))
Thanks for the response darkyam. I put this in the appropriate cell and get a "#REF!". Am I doing something wrong?
No, this was my fault. Replace the second 0 with 1. Sorry.
Brilliant. Works perfectly. Thanks!
What does the 9.9999...E+307 function return? I'm not familiar.
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.
If I've understood you can use INDEX rather than OFFSET to calculate the same but in non-volatile fashion (reduce calc overheads) eg:
The above also utilises a COUNT so as to ensure the Average will be returned if less than 30 days data exists.Code:=AVERAGE(INDEX($G6:$FC6,MATCH(9.99999999999999E+307,$G6:$FC6)-MIN(30,COUNT($G6:$FC6))+1):$FC6)
As with prior formulae we're assuming a contiguous data set.
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
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.
Last edited by shg; 11-17-2009 at 01:16 AM.
both work. thank you.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks