# Moving Averages

1. ## 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  Register To Reply

2. ## Re: Moving Averages

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.  Register To Reply

3. ## Re: Moving Averages

Hi NBVC,

Thanks for the help. This isn't quite there yet. When i drag the formula down, I only get the average in cell E8 instead of a moving average. For example,
E9 = 2 period moving average of cells D8:D9
E10 = 2 period moving average of cells D9:D10
E11 = 2 period moving average of cells D10:D11
etc.

Does that help? Thanks again!!  Register To Reply

4. ## Re: Moving Averages

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

5. ## Re: Moving Averages

I misunderstood...

Try: ``Please Login or Register  to view this content.``  Register To Reply

6. ## Re: Moving Averages Originally Posted by John Bates =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....  Register To Reply

7. ## Re: Moving Averages

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.  Register To Reply

8. ## Re: Moving Averages

Perfect, thanks!! Yeah, I was just trying to figure out what was going on with the formula; thanks for sending the update! BTW, why is OFFSET volatile? Is it because it can run into issues if you OFFSET back into a cell that doesn't exist (e.g., OFFSET up 3 cells from A2)?

Thanks,
John  Register To Reply

9. ## Re: Moving Averages

http://www.decisionmodels.com/calcsecretsi.htm  Register To Reply

10. ## Re: Moving Averages

Awesome, thanks again!  Register To Reply