Formula Errors

How to Restart a Cumulative Weighted Average in Excel

Olivia MillerOlivia Miller Oct 10, 2026 870 views

Question details

The user needs to calculate a cumulative weighted average that restarts from a specific column when a reprice or restart flag is detected in the dataset.

How to Restart a Cumulative Weighted Average in Excel
Product
Excel
Device & OS
not provided
Scenario
Working with tracking spreadsheets or financial models where the cumulative weighted average calculation must reset based on a conditional flag in a specific row.
Observed behavior
The user requires a nonvolatile formula structure to properly restart and calculate the weighted average for the new sequence without disrupting previous data.
Before you start

Ensure your spreadsheet is organized horizontally, with clearly defined rows for your restart flags, values, and weights.

Solution 1Recommended

Use SUMPRODUCT and SUM with Mixed References

Apply a combination of SUMPRODUCT and SUM formulas using anchored absolute references to reset your calculation at the exact column where the restart flag appears.

To restart a cumulative weighted average manually when a specific flag is met (e.g., when row 1 equals 1), you need to anchor your formula to the column where the restart occurs. This ensures that the calculation only considers values from the latest restart point forward.

1
Identify the restart column

Locate the column where your restart flag (e.g., the number 1) appears in row 1. For this example, assume the restart begins in column E.

2
Select the target cell

Click on the cell in column E where you want the new cumulative weighted average to be displayed.

3
Enter the formula

Input the formula =SUMPRODUCT($E3:E3,$E4:E4)/SUM(E$3:E3) into the cell. In this formula, row 3 represents your data values and row 4 represents your weights.

4
Adjust to your layout

Modify the row numbers in the formula to match the exact rows where your actual data and weights are stored.

5
Copy the formula across

Drag the fill handle to the right to copy this formula across subsequent columns. The relative parts of the reference will expand dynamically, calculating the cumulative average until the next restart flag is reached.

Use SUMPRODUCT and SUM with Mixed References
Formula Mechanics: The dollar signs ($) in the formula lock the starting column (Column E). As you drag the formula to the right, the endpoint of the range expands, creating a cumulative calculation from the restart point.
Seamless Formula Calculations

Calculate Complex Averages Easily in WPS Spreadsheet

WPS Spreadsheet offers powerful built-in functions, including SUMPRODUCT and SUM, allowing you to seamlessly manage dynamic calculations and weighted averages.

  1. 1. Open your dataset: Launch WPS Spreadsheet and open the file containing your data and restart flags.
  2. 2. Apply the SUMPRODUCT formula: Select the cell under the restart column and type =SUMPRODUCT($E3:E3,$E4:E4)/SUM(E$3:E3).
  3. 3. Drag to fill: Click and hold the small square at the bottom right of the cell, then drag across the row to instantly apply the cumulative calculation.
100% compatible with Microsoft Excel formulas and functionsFree, lightweight, and fast processing for large datasetsFamiliar user interface for quick adoption with zero learning curve
microsoft office alternative - wps office

Frequently Asked Questions

What is a cumulative weighted average?

A cumulative weighted average continually recalculates the average of a dataset by factoring in both the values and their varying weights as new data points are added across your spreadsheet.

Why use SUMPRODUCT instead of the AVERAGE function?

The AVERAGE function only calculates a simple arithmetic mean. To get an accurate weighted average, you must use SUMPRODUCT to multiply each value by its corresponding weight, and then divide that result by the sum of all weights using the SUM function.

How do mixed references work in cumulative formulas?

Mixed references use a dollar sign ($) to lock a specific row or column. In a formula like $E3:E3, the first part is locked to column E, while the second part is relative and changes as you copy the formula. This creates an expanding range necessary for cumulative calculations.