Moving Weighted Average formula

**Ori** · 08-17-2006, 03:59 PM

Hello all, I was wondering if anyone knew of an excel formula that would let me calculate a weighted moving average, for data that is going down a column in excel.

I have a formula for a simple moving average, however am having trouble thinking of one for a weighted moving average.

Dave F · 08-17-2006, 04:30 PM

This may help:http://www.swpp.org/newsletter/summe...cedtopics.html

"Ori" wrote:

>
> Hello all, I was wondering if anyone knew of an excel formula that would
> let me calculate a weighted moving average, for data that is going down
> a column in excel.
>
> I have a formula for a simple moving average, however am having trouble
> thinking of one for a weighted moving average.
>
>
> --
> Ori
> ------------------------------------------------------------------------
> Ori's Profile: http://www.excelforum.com/member.php...o&userid=37675
> View this thread: http://www.excelforum.com/showthread...hreadid=572825
>
>

**Ori** · 08-17-2006, 05:26 PM

Thanks Dave, however I was thinking of something more general, as I often need to change the moving average period, and a formula like that gets a bit cumbersome when the period is > 20.

Nikki · 08-17-2006, 05:30 PM

by weighted do you mean you have two columns and you want to get a WA?
if so you can use:
=sumproduct(columnA,ColumnB) which is =(a1xb1+a2*b2+...)/(a1+b2+...)

Hope this works, otherwise please provide more detail.

Regards-

"Ori" wrote:

>
> Hello all, I was wondering if anyone knew of an excel formula that would
> let me calculate a weighted moving average, for data that is going down
> a column in excel.
>
> I have a formula for a simple moving average, however am having trouble
> thinking of one for a weighted moving average.
>
>
> --
> Ori
> ------------------------------------------------------------------------
> Ori's Profile: http://www.excelforum.com/member.php...o&userid=37675
> View this thread: http://www.excelforum.com/showthread...hreadid=572825
>
>

**Ori** · 08-17-2006, 05:49 PM

No, I only have one column of data I want the MWA from.

eg: B1:B4 is the data, C3:C4 is the MWA.

3 period moving average in C3 is (B1*1 + B2*2 + B3*3)/(3 + 2 + 1)
3 period moving average in C4 is (B2*1 + B3*2 + B4*3)/(3 + 2 + 1)

etc

I cannot use another column and multiply the values, as the values I multiply by (the weights) change for each cell.

Hope this makes some kind of sense

.

Ori

**Ori** · 08-17-2006, 06:03 PM

Hmm, come to think of it, I may just have to write out that formula a few times. What I currently have (with non weighted MAs) is a cell at the top of the column which determines the period, so I can change that and all the formulas are linked to it.

I just cant figure out how to do that with a weighted average.

**empiler1** · 06-05-2010, 09:05 AM

Originally Posted by Ori

Hmm, come to think of it, I may just have to write out that formula a few times. What I currently have (with non weighted MAs) is a cell at the top of the column which determines the period, so I can change that and all the formulas are linked to it.

I just cant figure out how to do that with a weighted average.

In case anyone comes searching like I did, SUMPRODUCT() can do weighted moving average easily:

A1:A5 contains normalized weights (sum of them is 1)
B1:B1000 contains data
C5:C1000 contains WMAs

C5=SUMPRODUCT(B1:B5, A$1:A$5)

Then copy to all the other cells in C

**kapucino** · 06-05-2010, 12:46 PM

Originally Posted by Ori

Hmm, come to think of it, I may just have to write out that formula a few times. What I currently have (with non weighted MAs) is a cell at the top of the column which determines the period, so I can change that and all the formulas are linked to it.

I just cant figure out how to do that with a weighted average.

HI

i did some extensive testing on moving averages and other methods by modeling in Excel.

Attached are some WMA Weighted Moving average Examples. If you need more advanced methods such as Holt Winters aditive or multiplikative ( sort of moving averages too) let me know. Mind that with all that methods you have to calculate the error (Such as MSE) and user solver to minimze the Error and determine the weight at the same time.