Moving Averages

Hi,

I am trying to create a formula that will dynamically calculate a short-run and long-run moving average. The number of periods for each moving average can change depending on what is typed into cell C2 and cell C3.

For example, if $C$2=2, then the SRMA in cell E8 would be the average for Periods 1-2, or 594,443 (average of cells D7:D8). If $C$2=3, then cell E8 would be empty and cell E9 would be 602,394.

Thanks so much!
John

Try:

=IF($C7=$C$2,AVERAGEIF($C$7:$C$47,"<="&$C$2,$D$7:$D$47),"")

and

=IF($C7=$C$3,AVERAGEIF($C$7:$C$47,"<="&$C$3,$D$7:$D$47),"")

copied down.

I misunderstood...

Try:

Please Login or Register  to view this content.

Originally Posted by John Bates

What do you think about this formula that i just created?

=IFERROR(AVERAGE(OFFSET(D7,(-$C$2+1),0,$C$2,1)),"")
copied down E

I suppose you could use:

=IF(C7<$C$2,"",AVERAGE(OFFSET(D7,(-$C$2+1),0,$C$2,1)))

in order to get blanks until the first useful cell...

but, although shorter, the OFFSET function is volatile....

I don't know what's wrong with me... ignore my last stupid formulas...

try, non-volatile:

Please Login or Register  to view this content.

copied down.

This article explains the volatile functions and why they are volatile.

http://www.decisionmodels.com/calcsecretsi.htm