Weighted moving average

     

Weighted moving average is a data smoothing approach that gives more weight lớn more recent time periods. In this guide, we’re going lớn show you how to lớn calculate weighted moving average in Excel.

Bạn đang xem: Weighted moving average

Download Workbook

What is weighted moving average (WMA)?

Moving average is a technique for smoothing data to lớn reduce effects of random, short-term fluctuations. Weighted moving average is form of a moving average which assumes that current data points are more prominent since they are more relevant than earlier data points.


If you are interested in Simple Moving Average (SMA), check out How to calculate simple moving average in Excel article.

For example, for a 3-point WMA, the earliest data gets the lowest weight (0.2). While the second one gets a higher value, lượt thích 0.3, the recent event gets the highest, e.g., 0.5. The important point is that the sum of weight numbers should be equal khổng lồ 1. Once the weights are distributed, all you need to bởi is multiply the data with corresponding weight and take the average of the results.

DataWeightWeighted Data (Data * Weight)
100.22
290.38.7
440.522

WMA (Sum of Weighted Data / Period): 10.9

A common way to lớn calculate weights is lớn use consecutive numbers starting from 1. You need to divide each number by the sum of numbers to find each number"s percentage value. You can use the following formula to find the total.

*

Where n = number of periods.

For example, if you calculate weighted moving average over 4 points, the sum will be 10 by calculating (4 * (4 + 1)) / 2. Thus, the weights will be 0.1, 0.2, 0.3, 0.4.

Xem thêm: Chủ Đề 1: Tìm Số Phức Z Có Môđun Lớn Nhất Và Thỏa Mãn Điều Kiện Cho Trước

The numbers given here are just examples. You can adjust them depending on your data.

Tip:

If you are using Excel 365, you can generate these weights easily with a single formula:


=SEQUENCE(Number_of_periods)/((Number_of_periods*(Number_of_periods+1))/2)

The SEQUENCE function will generate an array of sequential numbers from 1 khổng lồ entered value which is number_of_periods in this case.

*

You can simplify this formula for future updates by using the LET function as well. The function can define in-formula named ranges và allows you to giảm giá with the cell reference only once.


=LET(n,Number_of_periods,SEQUENCE(n)/((n*(n+1))/2))

Calculating weighted moving average by AVERAGE Function

Once the weights are set, using the AVERAGE function is the easiest way lớn calculate weighted moving average. However, you need to multiply each data phối with a weight corresponding khổng lồ the period to be calculated.

You can bởi this multiplication by using the SUMPRODUCT function. If you use Excel 365 và have the dynamic array support, simply multiply the data with weight ranges.


=AVERAGE(B5:B7*$D$5:$D$7)

or

=SUMPRODUCT(B5:B7,$D$5:$D$7)/COUNT($D$5:$D$7)

*

Next step is to lớn copy the formula down along with the data. The important point is to lớn avoid using the $ signs in the range reference khổng lồ keep the range relative while keeping weight range absolute. Excel adjusts relative references based on the formula cell"s location. Thus, you vì not need khổng lồ update your formula each time.

Xem thêm: Bài Thuốc/ Cách Chưng Lê Trị Ho Bằng Quả Lê Kết Hợp Nhiều Cách Làm Độc Đáo Khác


You can learn about relative reference in How lớn create an Excel absolute reference and relative reference.

Chuyên mục: Tài chính