Using a reference to calculate moving average

J

jtujague

I have a column that calculates a moving average of the values in the
preceding column and would like to be able to use a reference cell embedded
in the moving average formula so that I can alter the reference cell, say
from 8 to 10, and the formula will adjust from an 8-day moving average to a
10-day moving average. Anyone know how to do this? Your help is greatly
appreciated!
 
J

JE McGimpsey

One way:

Name your reference cell, say, "days". Then

B10: =AVERAGE(OFFSET(A10,1-days,,days,1))
 
J

jtujague

Thank you!! I had not come across the OFFSET function yet. Big help. Happy
Holidays!
 
S

Shane Devenshire

Hi,

Here is another variation:

=AVERAGE(OFFSET(A1,,,F$1))

Where F1 contains the number of days you want in your moving average and A1
is the first cell to average. You can shorten it to

=AVERAGE(OFFSET(A1,,,N))

If you range name cell F1 N.

Here are two longer ways

=AVERAGE(INDIRECT("A"&ROW()&":A"&ROW()+F$1-1))
or
=AVERAGEIFS(D,D,">="&ROW(),D,"<="&ROW()+F$1-1)

(in 2007 where D is the range name of the entire range you want to use the
running average with.)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top