How to Restart a Cumulative Weighted Average in Excel
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.

- 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.
Ensure your spreadsheet is organized horizontally, with clearly defined rows for your restart flags, values, and weights.
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.
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.
Click on the cell in column E where you want the new cumulative weighted average to be displayed.
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.
Modify the row numbers in the formula to match the exact rows where your actual data and weights are stored.
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.

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. Open your dataset: Launch WPS Spreadsheet and open the file containing your data and restart flags.
- 2. Apply the SUMPRODUCT formula: Select the cell under the restart column and type =SUMPRODUCT($E3:E3,$E4:E4)/SUM(E$3:E3).
- 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.

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.




