How to Calculate a Weighted Moving Average in Google Sheets Using SUMPRODUCT

In quantitative analysis, technical trading, and demand forecasting, moving averages smooth out short-term fluctuations to highlight underlying trends. While a Simple Moving Average (SMA) assigns equal importance to all data points within a defined window, a Weighted Moving Average (WMA) allocates greater mathematical weight to more recent observations. By prioritizing recent data points over older historical figures, a WMA reacts faster to directional shifts while filtering out single-day volatility.

While Google Sheets does not feature a dedicated =WMA() built-in function, you can build a flexible, mathematically robust Weighted Moving Average using SUMPRODUCT, SEQUENCE, and dynamic array formulas.

Understanding the Weighted Moving Average Formula

A linearly weighted moving average assigns increasing integer weights from 1 up to the window size (N). The mathematical formula calculates the sum of each data point multiplied by its corresponding weight, divided by the total sum of all weights:

[ ext{WMA}_t = rac{sum_{i=1}^{N} (P_{t-N+i} imes i)}{sum_{i=1}^{N} i} ]

For example, in a 5-period WMA:

• The oldest observation receives a weight of 1.
• The newest observation receives a weight of 5.
• The denominator is (1 + 2 + 3 + 4 + 5 = 15).

Calculating a WMA Using SUMPRODUCT with Inline Arrays

Suppose column A contains dates and column B contains daily closing prices or sales figures, beginning in cell B2. To compute a 5-period Weighted Moving Average for the fifth data point (row 6):

=SUMPRODUCT(B2:B6, {1; 2; 3; 4; 5}) / SUM({1; 2; 3; 4; 5})

Formula Breakdown:

• B2:B6: The 5-row vertical range of numerical observations.
• {1; 2; 3; 4; 5}: An inline vertical array constant representing linear weights. Semicolons denote vertical rows, ensuring array dimensions match B2:B6 exactly.
• SUMPRODUCT: Multiplies each corresponding cell by its weight and returns the sum of products.
• SUM({1; 2; 3; 4; 5}): Calculates the normalising divisor (15).

You can drag this formula down column C. However, hardcoding arrays becomes unwieldy when working with 10-period, 20-period, or 50-period moving averages.

Building a Dynamic WMA Using SEQUENCE

To eliminate hardcoded constants and enable dynamic window adjustments, use the SEQUENCE function. The SEQUENCE(rows, [columns], [start], [step]) function automatically generates a dynamic array of integers.

For a 10-period WMA in row 11:

=SUMPRODUCT(B2:B11, SEQUENCE(10, 1, 1, 1)) / SUM(SEQUENCE(10, 1, 1, 1))

To make the period variable, place your desired window length (e.g., 10) in a dedicated configuration cell such as E1:

=SUMPRODUCT(OFFSET(B2, ROW()-ROW(B$2)-E$1+1, 0, E$1, 1), SEQUENCE(E$1, 1, 1, 1)) / SUM(SEQUENCE(E$1, 1, 1, 1))

Automating Column Calculations with MAP and LAMBDA

Modern Google Sheets formulas can spill results automatically without manually dragging fill handles. Combining MAP, LAMBDA, and LET allows you to calculate the entire moving average series across hundreds of rows with a single cell formula.

Enter the following formula in cell C2:

=LET(
  data, FILTER(B2:B, ISNUMBER(B2:B)),
  n, 5,
  weights, SEQUENCE(n, 1, 1, 1),
  wSum, SUM(weights),
  totalRows, ROWS(data),
  MAP(SEQUENCE(totalRows), LAMBDA(r,
    IF(r < n,
      NA(),
      SUMPRODUCT(CHOOSEROWS(data, SEQUENCE(n, 1, r - n + 1, 1)), weights) / wSum
    )
  ))
)

How This Formula Functions:

1. LET: Defines local scoped variables (data, n, weights, wSum) to avoid redundant recalculation.
2. MAP(SEQUENCE(totalRows), LAMBDA(r, ...)): Iterates through each row index r from 1 to the end of the data set.
3. IF(r < n, NA(), ...): Returns #N/A for early rows where insufficient historical periods exist, preventing inaccurate early averages.
4. CHOOSEROWS(data, SEQUENCE(n, 1, r - n + 1, 1)): Slices the sliding window of n rows ending at row r and feeds them directly into SUMPRODUCT.

Exponential vs Linear Moving Averages

While the Weighted Moving Average applies linear weighting that terminates abruptly outside the window period, an Exponential Moving Average (EMA) applies exponentially decaying weights across all historical data. In Google Sheets, a linear WMA generated with SUMPRODUCT is ideal when you require strict finite impulse response (FIR) filtering where events older than (N) periods exert zero influence on current projections.