auto average over time
Currently i have to produce a report each week against a whole load of stats, i then have to provide a rolling average across the previous 4 weeks
I have set up a spreadsheet which i add to each week but only display the previous 4 weeks stats to the user, looks something like this
wk 1 wk2 wk3 wk4 average
stat 1 99 98 99 98 98.5
stat 2 99 98 99 98 98.5
....etc
each week I add a new column in after the previous week and then hide the previous week 1 column.
My issue is that each week i have to manually edit the average column to look at the right 4 columns as i am using simply =SUM(V20:Y20)/4 for example. Is there anyway i can set it so that it always looks at the 4 columns immediately to the left of the average column?
To obtain the average of the four cells left of the cell containing the formula
=AVERAGE(OFFSET(INDIRECT(ADDRESS(ROW(),COLUMN())),0,-1,1,-4))
Last edited by sweep; 03-09-2010 at 05:56 PM.
Please disregard anything in the above post. It may well have been edited without my consent, as has been the case with several posts and threads recently.
If you put your formula to the left of the data, then you can eliminate inserting columns. For example,
The formula in A2 and down is--A--- -B- -C- -D- -E- -F- 1 Last 4 wk1 wk2 wk3 wk4 wk5 2 98.75 99 98 99 98 100 3 98.5 99 98 99 98
=AVERAGE(INDEX(2:2, MATCH(9E+307, 2:2)):INDEX(2:2, MATCH(9E+307, 2:2) - 3))
Microsoft MVP - Excel
Entia non sunt multiplicanda sine necessitate
I'm with shg that storing to the left would be advised.
If for whatever reason you can't do this then below is a non-volatile alternative to your existing approach:
Z20: =AVERAGE(INDEX($A20:Z20,COLUMN(Z20)-4):INDEX($A20:Z20,COLUMN(Z20)-1))
As the new column is inserted (we assume between existing Y and existing Z) the above will adapt accordingly.
(in the above we're assuming there is no need to test for fewer than 4 available "prior week" columns)
My Recommended Reading:
Volatility
Sumproduct & Arrays
Pivot Intro
Email from XL - VBA & Outlook VBA
Function Dictionary & Function Translations
Dynamic Named Ranges
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
Reply With Quote
Bookmarks