Weighted moving average excel

How to calculate weighted moving average in Excel

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

What is weighted moving average (WMA)?

Moving average is a technique for smoothing data to 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.

For example, for a 3-point WMA, the earliest data gets the lowest weight (0.2). While the second one gets a higher value, like 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 to 1. Once the weights are distributed, all you need to do is multiply the data with corresponding weight and take the average of the results.

Data	Weight	Weighted Data (Data * Weight)
10	0.2	2
29	0.3	8.7
44	0.5	22

WMA (Sum of Weighted Data / Period): 10.9

A common way to calculate weights is to 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.

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

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

The SEQUENCE function will generate an array of sequential numbers from 1 to 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 and allows you to deal with the cell reference only once.

Calculating weighted moving average by AVERAGE Function

Once the weights are set, using the AVERAGE function is the easiest way to calculate weighted moving average. However, you need to multiply each data set with a weight corresponding to the period to be calculated.

You can do this multiplication by using the SUMPRODUCT function. If you use Excel 365 and have the dynamic array support, simply multiply the data with weight ranges.

Next step is to copy the formula down along with the data. The important point is to avoid using the $ signs in the range reference to keep the range relative while keeping weight range absolute. Excel adjusts relative references based on the formula cell’s location. Thus, you do not need to update your formula each time.

Источник

Как найти взвешенные скользящие средние в Excel

Взвешенное скользящее среднее — это метод, который можно использовать для сглаживания данных временных рядов, чтобы уменьшить «шум» в данных и упростить выявление закономерностей и тенденций.

Вся идея взвешенного скользящего среднего состоит в том, чтобы взять среднее значение за определенное количество предыдущих периодов, чтобы получить «среднее» значение за данный период, придавая больший вес более поздним периодам времени.

В этом руководстве мы покажем, как найти взвешенные скользящие средние для данных временных рядов в Excel.

Пример: взвешенные скользящие средние в Excel

Предположим, у нас есть следующий набор данных, который показывает продажи определенной компании за 10 периодов:

Мы можем выполнить следующие шаги для расчета взвешенных скользящих средних за этот период времени:

Шаг 1: Решите, сколько предыдущих периодов включить в расчет взвешенного скользящего среднего.

Мы будем использовать три периода для этого примера.

Шаг 2: Решите, какие веса присвоить каждому периоду.

Мы назначим веса следующим образом:

• 0,5 за текущий период
• 0,3 за предыдущий период
• 0,2 за два периода назад

Обратите внимание, что общий вес должен составлять 1.

Шаг 3: Рассчитайте взвешенное скользящее среднее для каждого периода.

На изображении ниже в столбце C показано взвешенное скользящее среднее (WMA) за период времени 3, а в столбце D показана формула, которую мы использовали для его расчета:

Мы можем использовать аналогичную формулу, чтобы найти взвешенное скользящее среднее для каждого периода времени:

Если мы создадим линейный график, чтобы визуализировать фактические продажи по сравнению со взвешенной скользящей средней, мы заметим, что линия WMA более гладкая с меньшим количеством пиков и впадин. В этом вся идея взвешенной скользящей средней — она позволяет нам увидеть истинный основной тренд данных без лишнего шума.

Внесение корректировок во взвешенное скользящее среднее

Есть два числа, которые вы можете настроить, что приведет к различным расчетам взвешенного скользящего среднего:

Количество использованных предыдущих периодов.В нашем примере мы использовали три предыдущих периода для расчета взвешенных скользящих средних, но мы могли бы выбрать 4, 5, 6 и т. д. Как правило, чем больше периодов вы используете в своих расчетах, тем более гладкой будет линия взвешенной скользящей средней. будет.

Веса, присвоенные каждому периоду.В нашем примере мы присвоили весовые коэффициенты 0,5, 0,3 и 0,2, но мы могли бы выбрать любую комбинацию весовых коэффициентов, если в сумме они дают 1. Как правило, чем больший вес вы придаете самому текущему периоду, тем менее гладкой будет линия взвешенного скользящего среднего.

Чтобы проиллюстрировать это, рассмотрим, если бы мы снова использовали три периода для нашего расчета WMA, но вместо этого использовали следующие веса:

• 0,7 за текущий период
• 0,2 за предыдущий период
• 0,1 за два периода назад

Поскольку мы придали слишком большое значение текущему периоду времени, линия взвешенного среднего скользящего значения была бы менее гладкой и больше напоминала бы фактическую линию продаж:

Простые скользящие средние против взвешенных скользящих средних

Простое скользящее среднее — это способ расчета скользящего среднего, при котором все периоды времени, используемые при расчете, имеют одинаковый вес.

Например, если вы используете три периода времени для расчета скользящего среднего, то вес, присвоенный каждому периоду времени, будет равен 0,333. Или, если вы используете четыре периода времени для расчета скользящей средней, тогда вес, присвоенный каждому периоду, будет равен 0,25.

Простую скользящую среднюю вычислить проще, но преимущество использования взвешенной скользящей средней заключается в том, что вы можете присвоить более высокие веса более поздним периодам. Это полезно, если ваши данные имеют тенденцию в определенном направлении, и вы хотите получить более точное представление о тенденции.

Например, предположим, что вы вычисляете взвешенное скользящее среднее для очков, набранных баскетболистом, который становится все лучше и лучше по ходу сезона.

Используя скользящее среднее за пять игр, вы хотели бы придать большее значение очкам, набранным в их последней игре, чтобы вы могли получить более точное представление о том, сколько очков они должны набрать.

Дополнительные ресурсы

В следующих руководствах объясняется, как рассчитать другие распространенные показатели в Excel:

Источник

How to Find Weighted Moving Averages in Excel

A weighted moving average is a technique that can be used to smooth out time series data to reduce the “noise” in the data and more easily identify patterns and trends.

The whole idea behind a weighted moving average is to take the average of a certain number of previous periods to come up with an “average” value for a given period, while giving more weight to more recent time periods.

In this tutorial, we show how to find weighted moving averages for time series data in Excel.

Example: Weighted Moving Averages in Excel

Suppose we have the following dataset that shows the sales for a certain company during 10 periods:

We can perform the following steps to calculated weighted moving averages for this time period:

Step 1: Decide how many previous periods to include in the weighted moving average calculation.

We’ll use three periods for this example.

Step 2: Decide what weights to assign each period.

We’ll assign the weights as follows:

• 0.5 for the current period
• 0.3 for the previous period
• 0.2 for two periods back

Note that the total weights must add up to 1.

Step 3: Calculate the weighted moving average for each period.

In the image below, column C shows the weighted moving average (WMA) for time period 3 and column D shows the formula we used to calculate it:

We can use a similar formula to find the weighted moving average for every time period:

If we create a line chart to visualize the actual sales vs. the weighted moving average, we’ll notice that the WMA line is more smooth with less peaks and valleys. This is the whole idea behind a weighed moving average – it allows us to see the true underlying trend of the data without the extra noise.

Making Adjustments to the Weighted Moving Average

There are two numbers you can adjust that will lead to different weighted moving average calculations:

The number of previous periods used. In our example we used three previous periods to calculate the weighted moving averages, but we could have chosen 4, 5, 6, etc. As a rule of thumb, the more periods you use in your calculations, the smoother the weighed moving average line will be.

The weights assigned to each period. In our example we assigned the weights as 0.5, 0.3, and 0.2, but we could have chosen any combination of weights as long as they added up to 1. As a rule of thumb, the more weight you give to the most current period, the less smooth the weighted moving average line will be.

To illustrate this, consider if we again used three periods for our WMA calculation but instead used the following weights:

• 0.7 for the current period
• 0.2 for the previous period
• 0.1 for two periods back

Since we gave so much weight to the current time period, the weighted moving average line would be less smooth and more closely resemble the actual sales line:

Simple Moving Averages vs. Weighted Moving Averages

A simple moving average is a way to calculate a moving average in which all time periods used in the calculation are given the same weight.

For example, if you use three time periods to calculate the moving average then the weight given to each time period would be 0.333. Or if you use four time periods to calculate the moving average then the weight given to each period would be 0.25.

A simple moving average is more straightforward to calculate, but the benefit of using a weighted moving average is that you can assign higher weights to more recent periods. This is useful if your data is trending in a certain direction and you want to get a more accurate idea of the trend.

For example, suppose you’re calculating the weighted moving average for points scored by a basketball player who is getting better and better as the season goes on.

Using a five-game moving average, you would want to place more weight on the points scored in their most recent game so that you can get a more accurate idea of how many points they’re expected to score.

Additional Resources

The following tutorials explain how to calculate other common metrics in Excel:

Источник

How to calculate Simple, Weighted, and Exponential Moving Averages in Excel

In your school life, you have learned about average and the method to calculate it. The formula to calculate the average is very simple. You just have to add all the values in the given data and divide the result by the total number of values in the data. Moving average is also another type of average that has real-life applications. It has applications in many areas like the stock market, sales, etc.

Moving average is of three types, simple weighted and exponential. In this article, we will explain the methods to calculate all three types of moving averages in Excel.

How to calculate Moving Average in Excel

We will calculate the moving average in Excel using two methods. In one method, we will use a built-in tool of MS Excel and in the other method, we will use formulae.

1] Using Data Analysis Tool Pack

In this method, we will use the Data Analysis Tool Pack in Excel to calculate the moving average. MS Excel comes with a tool to calculate simple moving averages. The Data Analysis Tool Pack is an add-in, which means you may not have it by default. We are listing here the steps to get this tool.

1] Click on the “File” option.

2] Select “Options.”

3] This will open a dialog box. Click on “Add-Ins.”

4] On the right panel of the dialog box, you will see a drop-down menu at the bottom. Select “Excel Add-ins” in the drop-down menu and click on the “Go” button.

5] Now select “Analysis ToolPack” and click on the “OK” button.

6] The above steps will install the Data Analysis Tool on MS Excel. You can view it in the “Data” tab.

Now you are ready to calculate the simple moving average. To show you the calculation method, we have created sample data of varying temperatures for the first 10 days in a month.

Please follow the below-listed steps to calculate the simple moving average using Data Analysis Tool Pack.

1] Click on the “Data” tab and select the “Data Analysis” option as shown in the above screenshot (see step 6).

2] A small dialog box will open, where you have to select the “Moving Average” option and click OK. It is the simple moving average.

3] You have to enter the input range of the data for which you want to calculate the simple moving average. In our sample data, we have data ranges from cell B2 to cell B11. Therefore, we entered the range B2:B11. After that, enter the “Interval.” In the output range, you have to enter the address of the cell in which you want to get the output range. We selected E2. When you are done, click OK.

4] You will get the result. You can see in the below screenshot, the first three rows are showing #N/A error. This is because we have entered 4 in the “Interval,” which indicates that it is a 4 days SMA.

In method 1 above, we have calculated the moving average with the help of a tool in Excel. But using method 1, we can calculate only the simple moving average. In method 2, we will calculate all three types of moving averages using formulae.

2] Calculation of Simple Moving Average (SMA) using Formula

We will take the same sample data here.

1] If you want to calculate the 4 days SMA, you have to enter the following formula in the cell that lies on row 5 (Day 4). After entering the formula, press “Enter.”

2] Now, hover your cursor on the bottom right corner of the selected cell. When your cursor changes to the “Plus” icon, press and hold the left click of your mouse and drag it to the E11 cell. This will copy the formula to the other cells.

3] You will get your result.

3] Calculation of Weighted Moving Average (WMA) using Formula

Let’s calculate the weighted moving average. Again, we are taking the same sample data. To calculate the WMA, you should have the weights assigned to the particular values. The formula to calculate the weighted moving average is:

We are calculating here 3 point WMA with 70% weight is assigned to the latest value, 20% to the value just before it, and 10% to the value before the second one. According to this data, you have to enter the following formula in the E4 cell.

The first two cells are empty because we are calculating three-point WMA. Now, drag the cell to the last cell as we have done before in SMA calculation. You will get your result.

4] Calculation of Exponential Moving Average (EMA) using Formula

To calculate EMA, we should have the first EMA value, which we get by calculating the SMA and the weight multiplier or smoothing constant (K). The basic formula to calculate the EMA is as follows:

1] Let’s take the same table in which we have calculated the SMA. In this example, we have taken K = 0.3. The first EMA value is equal to the first SMA value. We are calculating here the 4 days SMA and EMA values.

2] Now apply the following formula to the cell F6.

Do note that, we have locked the cell G in the above formula because we need the same value of K for all EMA calculations.

3] Now, Drag the cell to copy the formula to the remaining cells.

That’s it, we have calculated all three types of moving averages in MS Excel. We hope you enjoyed the article.

Источник