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?


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


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



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


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)