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)
