# 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

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.

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!!

4. ## Re: Moving Averages

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

5. ## Re: Moving Averages

I misunderstood...

Try:

``Please Login or Register  to view this content.``

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....

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.

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

9. ## Re: Moving Averages

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

10. ## Re: Moving Averages

Awesome, thanks again!

There are currently 1 users browsing this thread. (0 members and 1 guests)

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1