MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Moving Average question for a newbie


Posted by RGE on February 02, 2002 5:50 PM

How do I calculate a 3 day moving average that continually changes when new numbers are added to the column?

Example:

a1:Date b1:Cost c1:3day moving average cost
a2:1/1/2002 b2:10 c2: EXCEL FORMULA
a3:1/2/2002 b3:5
a4:1/3/2002 b4:10
a5:1/4/2002 b5:20
a6:1/5/2002 b6:

In this example, C2 should be 11.6666. Lets say my cost number for 1/5/2002 is 10. When I enter 10 into b6, I would like C2 to change to 13.333 ((10+20+10)/3).

This is probably too simple for this board, but I'm drawing a blank...

Thanks in advance...

Matt (RGE)


Posted by Bariloche on February 02, 2002 8:27 PM

RGE,

The following formula, entered into cell C2, will give you what you want.

=IF(COUNT(B2:B65536)<3,AVERAGE(B2:B3),AVERAGE(OFFSET(B2,COUNT(B2:B65536)-1,0,-3,1)))


enjoy

Posted by Larry Kramer on February 02, 2002 8:40 PM


If the dates will be entered sequentially, name cells a1:a10000 or so "DateRange" and cells b1:b10000 or so "CostRange"

then your formula in c2 can be

=(INDEX(CostRange,MATCH(MAX(DateRange),DateRange,0),1)+INDEX(CostRange,MATCH(MAX(DateRange),DateRange,0)-1,1)+INDEX(CostRange,MATCH(MAX(DateRange),DateRange,0)-2,1))/3

If you can spare the space, you might want to have c2, c3, and c4 represent each of these elements and then make c5 =average(c2:c4)