Dynamic Moving Average

  • Thread starter Thread starter Steve
  • Start date Start date
S

Steve

Hi All,

I would really appreciate any help in trying to solve the following problem:

I have a forecast model and I am trying to create a dynamic moving average.
Specifically, I need a formula to look at a cell, which contains the
current month i.e. June. It then needs to look up the month in a column,
which contains all the months of the year. Once it finds the match, it needs
to take the value in the next column over as well as the value of the prior
two months before it and calculate the average of these three months.

The calculation needs to update whenever the month value is changed.

Any suggestions would be greatly appreciated.

Thanks,

Steve
 
Ok, this is how I would handle it. I would create a small
table somewhere for your month to be looked up on and I
would create a table using the month NUMBERS. I am going
to use the LOOKUP function and it wont work unless the
table is in alphabetical order.

I would make the first column the month numbers, the
second column would be a formula and a third column which
would be your month totals. In the middle columns use the
AVERAGE function to create the numbers you will have over
with your main data.

EXAMPLE:
X Y Z
1 Month Average Total
2 1 =AVERAGE(Z2,Z6,Z5) 23
3 2 =AVERAGE(Z3,Z2,Z6) 64
4 3 =AVERAGE(Z4,Z3,Z2) 33
5 4 =AVERAGE(Z5,Z4,Z3) 15
6 5 =AVERAGE(Z6,Z5,Z4) 22

Then I would create my main table using a LOOKUP function

EXAMPLE:
A B
1 Month Average
2 1 LOOKUP(A2,$X$2:$X$6,$Y$2:$Y$6)
3 2 LOOKUP(A3,$X$2:$X$6,$Y$2:$Y$6)
4 3 LOOKUP(A4,$X$2:$X$6,$Y$2:$Y$6)
5 4 LOOKUP(A5,$X$2:$X$6,$Y$2:$Y$6)
6 5 LOOKUP(A6,$X$2:$X$6,$Y$2:$Y$6)

The reason we will use the $ in the LOOKUP is so you can
copy and paste it to any cells and it will always find
the table you made in the same location. Now you should
be able to change your months totals in column Z and it
will average it out for you and refer it to the main
portion of the table.

BillJunior
 
Hi Steve,

Assumptions:

1) A1 contains the date
2) List of months down Column A starts at A3
3) Corresponding values in Column B

Enter this formula in B1:

=AVERAGE(OFFSET($B$2,MATCH($A$1,$A$3:$A$14,0)-2,0,3,1))

Hope this helps!
 
Hi Bill Junior and Domenic,

Thank you both for your suggestions.

Domenic, I used your approach as it seemed the easier of the two and it
works great.

I really appreciate both of your suggestions.

You guy's rock.

Cheers,

Steve
 
Steve,

Just a couple of changes to avoid a circular reference:

1) Start your column of dates at A5, instead of A3

2) Change the formula to
=AVERAGE(OFFSET(B4,MATCH($A$1,$A$5:$A$16,0)-2,0,3,1))

Of course, if you choose January or February as your date, you won't get
a three month average, right?

Hope this helps!
 
Back
Top