Excel Weighted Moving Average: Formula & Setup

Formulas & functions · Updated November 22, 2026

In Excel, a weighted moving average (WMA) is calculated by multiplying each data point in a rolling window by a set of weights (which sum to 1 or 100%) and then summing those products. You can build it with the SUMPRODUCT function combined with relative references, or use the AVERAGE function on pre-weighted data; Excel does not have a built-in WMA function, but the FORECAST.ETS function can apply exponential smoothing, which is a related weighting scheme.

What Is a Weighted Moving Average?

A moving average smooths out short-term fluctuations in time-series data (like sales, stock prices, or temperatures) by averaging a fixed number of recent observations. A simple moving average (SMA) gives each observation in the window the same weight. A weighted moving average assigns different weights to each observation, typically giving more importance to recent data. For example, a 3-period WMA might use weights of 0.5 (most recent), 0.3, and 0.2 (oldest). This makes the average react faster to new trends while still filtering out noise.

WMAs are widely used in technical analysis, inventory forecasting, and quality control because they reduce lag compared to an SMA. The key difference is that the weights are chosen by you, not by the data, so you control how much emphasis each period gets.

How to Calculate a Weighted Moving Average in Excel

There is no single built-in WMA function, but you can use SUMPRODUCT in a few minutes. Here’s a step-by-step setup for a 3-period WMA with weights 0.5, 0.3, and 0.2.

  1. Place your data in column A, starting at A2 (e.g., sales figures for months Jan–Dec). Put the dates or labels in column B if you like.
  2. In cells D1, E1, and F1, enter your weights: 0.5, 0.3, 0.2. Make sure they sum to 1. (You can use percentages formatted as decimals.)
  3. In cell C4 (assuming your first data point is in A2), enter the formula: =SUMPRODUCT(A2:A4, $D$1:$F$1)
  4. Drag the fill handle down from C4 to the last cell where you have three prior data points. For a 3-period WMA, the first result appears in row 4 (data rows 2–4).

For a 5-period WMA with weights in G1:K1 (e.g., 0.3, 0.25, 0.2, 0.15, 0.1), the formula in the first cell would be =SUMPRODUCT(A2:A6, $G$1:$K$1).

If you prefer to avoid helper cells, you can hard-code the weights inside the formula, like this: =SUMPRODUCT(A2:A4, {0.5,0.3,0.2}). However, using cells makes it easier to adjust weights later.

Choosing the Right Weights and Window Size

The weights and the window length (number of periods) are the two decisions you must make. There is no universally “correct” set; it depends on your data and goals.

  • Window length: A shorter window (e.g., 3 periods) responds faster to changes but is noisier. A longer window (e.g., 10 periods) is smoother but lags more. Start with a window that matches the cycle length of your data (e.g., 12 for monthly seasonal data).
  • Weights: Common patterns include linearly increasing weights (e.g., 1, 2, 3 for a 3-period WMA, then normalized to sum to 1) or exponentially decreasing weights (e.g., 0.5, 0.3, 0.2). The most recent period should usually have the highest weight.
  • Normalization: Weights must sum to 1 (or 100%). If you use integer weights like 1, 2, 3, divide each by the sum (6) to get 0.1667, 0.3333, 0.5.

To test different weights, you can use Excel’s Goal Seek (Data > What-If Analysis > Goal Seek) to find weights that minimize forecast error, but that’s advanced. For most purposes, a simple linear weighting works well.

Common Problems and How to Fix Them

Several issues can trip up users when building a WMA:

  • Weights not summing to 1: If your weights sum to something other than 1, your WMA will be biased. Always check with =SUM(D1:F1) and adjust.
  • Incorrect range alignment: The data range and the weight range must have the same number of cells. If you use a 3-period window, your data range must be exactly 3 cells. A mismatch causes a #VALUE! error.
  • Blank cells or text: SUMPRODUCT treats blank cells as 0, which skews the result. Make sure your data has no blanks or replace them with 0 only if that makes sense.
  • Dragging formulas with absolute references: Use dollar signs ($D$1:$F$1) to lock the weight range, or the reference will shift when you drag down, causing errors.

Another common mistake is confusing a weighted moving average with an exponential moving average (EMA). EMA applies weights that decrease exponentially and never reach zero; it’s calculated recursively. Excel does have a built-in EMA through the FORECAST.ETS function (in Excel 2016 and later), but that’s for forecasting, not for smoothing historical data. For a true EMA, you’d need to write a formula like =B2*alpha + C1*(1-alpha) where alpha is your smoothing constant.

Weighted Moving Average vs. Simple and Exponential

Choosing between SMA, WMA, and EMA depends on how quickly you want the average to react.

  • Simple Moving Average (SMA): All periods equally weighted. Easy to compute with =AVERAGE(A2:A4). Best for stable data with no strong trend.
  • Weighted Moving Average (WMA): You control the weights. More flexible than SMA, but you must choose weights manually. Good for data with a known recent bias.
  • Exponential Moving Average (EMA): Weights decrease exponentially, giving the most recent data the highest weight. It reacts faster than a linear WMA and is often preferred in trading. In Excel, you can compute it with a recursive formula or use FORECAST.ETS for forecasting.

In practice, a WMA with linearly increasing weights (e.g., 1,2,3) behaves similarly to an EMA, but the EMA is smoother and requires only one parameter (alpha) instead of a full weight set.

Using a Weighted Moving Average for Forecasting

While a WMA is primarily a smoothing technique, you can use it for simple forecasting. The forecast for the next period is simply the WMA of the last N periods. For example, if your last three sales were 100, 110, and 120, and your weights are 0.5, 0.3, 0.2, the forecast for the next period is =0.5*120 + 0.3*110 + 0.2*100 = 113.

To set this up in Excel, you can extend the formula one row beyond your data. For instance, if your data is in A2:A13, put the WMA formula in C14 using =SUMPRODUCT(A11:A13, $D$1:$F$1). That gives a forecast for period 14.

If you want a more sophisticated forecasting method that automatically optimizes weights, consider using the FORECAST.ETS function, which applies exponential smoothing with seasonality detection. For example, =FORECAST.ETS(14, A2:A13, B2:B13) returns a forecast based on historical data and time stamps. This function is available in Excel 2016 and later, but not in older versions.

Frequently Asked Questions

Does Excel have a built-in weighted moving average function?

No, Excel does not have a dedicated WMA function. You can use SUMPRODUCT to create one, or use the FORECAST.ETS function for exponential smoothing, which is a type of weighted average. For a true WMA, you must supply the weights yourself.

How do I make a weighted moving average chart in Excel?

First, calculate the WMA values in a column as described above. Then select both your original data and the WMA column, and insert a line chart (Insert > Charts > Line). The WMA line will appear smoother than the original data. You can also add a trendline, but that uses a different algorithm.

What weights should I use for a 5-period weighted moving average?

A common choice is linearly increasing weights: 1, 2, 3, 4, 5. Normalize them by dividing by the sum (15) to get 0.0667, 0.1333, 0.2, 0.2667, 0.3333. Alternatively, you can use exponential weights like 0.5, 0.25, 0.125, 0.0625, 0.0625 (the last two equal to sum to 1). The best choice depends on how much you want to emphasize recent data.

Can I use a weighted moving average for stock prices?

Yes, traders often use WMAs to smooth stock prices and identify trends. A common setup is a 5-period WMA with weights 0.3, 0.25, 0.2, 0.15, 0.1. However, many prefer the EMA because it is easier to compute and reacts faster. Remember that moving averages are lagging indicators and should not be used alone for trading decisions.

Why is my weighted moving average formula returning #VALUE!?

This usually means your data range and weight range have different sizes. For example, if you use =SUMPRODUCT(A2:A4, D1:F1), both ranges must have exactly 3 cells. Also check that your weights are numbers, not text. If you have any text or blank cells in the data range, SUMPRODUCT may return an error.

What is the difference between a weighted moving average and a weighted average?

A weighted average applies weights to a set of values to get a single average (e.g., a grade point average). A weighted moving average is a series of weighted averages over a rolling window, used for time-series smoothing. In Excel, the SUMPRODUCT formula is the same, but a WMA is applied to a sliding range.

Related

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.