Excel Moving Average? 1

[TomYC]

This should be pretty simple,right?
I'd like to have a cell calculate a moving average (say, 6 months, or 6 columns of data) of a row of values, where the count of values increases by one every month, i.e. as each new month of data appears, the moving average calculates using only the right-most six columns in any particular row.
I'm working with OFFSET, as in =OFFSET($A2,0,COUNTA(2:2)-6,1,6) and I know that comes up with the #VALUE! error, but I think the next step takes care of that--does it involve Named Ranges?
Tnx

 

[xlhelp]

[blue]=AVERAGE(OFFSET(A2,COUNT(A:A)-1,,-6))[/blue]

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[TomYC]

Thank you, xlhelp--
That didn't work for me, so I will show you where I'm at presently:
=AVERAGE(OFFSET(A2,0,COUNTA(2:2)-7,1,6)) is my 6mAvg formula
Where I have a table of data thus:
Units JAN FEB MAR APR MAY JUN JUL AUG SEP OCT NOV DEC 6mAvg
MONTI 2.4 3.5 1.8 3.4 4.8 0.8 2.9 1.4

Of course, when I get this, I want to replace the COUNTA function with one that will count only non-error values, as my future month formulas are currently either #NA! or #DIV/0! until referenced data comes in. But that's for later!

 

[xlhelp]

In that case, it's
=AVERAGE(OFFSET(A2,,COUNT(2:2)-1,,-6))

Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[TomYC]

Thanks, again, xlhelp--
I slightly modified your suggestion, and additionally I put my moving average cells in a separate worksheet, referencing back to the worksheet in which I have placed 2009 data as well, so that I can have a Jan 2010 rolling average based upon Aug09-Jan10 data columns. Thus in my 6MoAverage worksheet, in each cell for each month of 2010 I have a version of your formula. As it turns out, the formula that works for Aug 2010 (i.e. 6 mos. average including Aug) is:
=AVERAGE(OFFSET('Fall Rate 2009-2010'!$A2,,COUNT('Fall Rate 2009-2010'!2:2)-0,,-6))
That "-0" I achieved through trial and error, and I now have formula for July's rolling average:
=AVERAGE(OFFSET('Fall Rate 2009-2010'!$A2,,COUNT('Fall Rate 2009-2010'!2:2)-1,,-6)), and June's, with "-2" etc.
But surely this is either wrong (though I am oonfirming the correct results, manually) or inelegant! What am I missing here?

 

[SkipVought]

Hi,

Regarding the -0, -1 hard coded values in your two formulas...

Rather use a cell reference in which the 0 or 1 can be entered.

Or if this formula is used in various cells, use a formula in place of these har coded values that will return the desired value based on context.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[TomYC]

Exactly what I want--
However it now occurs to me that, since I am interested in trending data, i.e. a 6 month rolling average for each month in the year, I don't really want the COUNT function, which returns a dynamic result based upon how many months (columns!) of data I have.
So if I have historical data in monthly columns from 2009 (for sake of argument) through August of 2010 (anticipating data through December 2010, meaning the column that is 24 columns offset from my column A (with a Unit name), that means that my formula for December 2010's 6 month rolling average will be:
=AVERAGE(OFFSET('Fall Rate 2009-2010'!$A2,,19,,6))
That is to say, the average of the six columns beginning with the one that is 19 away from the reference cell $A2.
Still doesn't address Skip's advice to use a cell reference instead of a hard number, so I'm not done yet!

 

[xlhelp]

TomYC.

Really, really, really (one more just for fun), really very bad idea to have unwarranted constants in the formula. It causes immense amount of trauma when data changes.

As I see it,
=AVERAGE(OFFSET('Fall Rate 2009-2010'!$A2,,COUNT('Fall Rate 2009-2010'!2:2)-1,,-6))
should work unless you have some other weirdness going on in between.

The only reason the minus one is there is because the function is not capable of giving us what we need. And since we are doing a six month average, the 6 is not likely to change.

Shouldn't matter how long your data is. I do a 12 week average every week using the same formula and my data dates back to Jan 2008. And why do you want COUNTA? Do you really have strings instead of numbers? Then whole question is redundant.


Canadian eh! Check out the new social forum Tek-Tips in Canada.
With the state of the world today, monkeys should get grossly insulted when humans claim to be their decendents

 

[TomYC]

Thank you, xlhelp--
That works for me, and I can adjust the offset to accommodate that my data source also has a totals column, i.e. one more non-blank cell in each row.
Perhaps I did not make it clear that I am interested in an historical rolling average: For August, I want the average of March through August, which is what I get here, but my destination "grid" will want to have a number for July, calculating the average of February through July, and so on, both forward (in time) and backward (in which January calculates the average of Aug 09 through Jan 2010). That's why I'm beginning to wonder if I can use a count function at all--

 

[SkipVought]

EITHER you have a fixed number or cells in your range that assumes x months per cell or you have expressions to bound your range by date values that can be found in headings. Or do you have BOTH or NEITHER?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}