How to Carry a Weekly Shortfall Forward in WPS Office Excel with a Formula
Question details
The user needs an Excel formula that calculates the next week's required target by adding the previous week's shortfall or surplus to a fixed weekly target.

- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- The user is tracking weekly performance or budgets against a fixed weekly target and wants future targets to adjust automatically based on the previous week's result.
- Observed behavior
- The next week's target should equal the fixed weekly target plus the previous week's shortfall or surplus, but the user needs the correct formula to implement it.
Keep the fixed weekly target in a separate cell, such as E1, and arrange the required weekly targets in column A and actual results in column B so the formula can be filled down without manual row-by-row edits.
Calculate the Required Weekly Target with a Carry-Forward Formula
Use this approach when you want each week's target to automatically include the previous week's shortfall or surplus.
This formula keeps the fixed target in one cell and adjusts future targets by the difference between the previous target and the actual result.
Type your fixed weekly target, for example 126, into cell E1.
In cell A2, enter the formula =E1 to start the first week at the fixed target.
In cell A3, enter =$E$1+A2-B2 to add the previous week's shortfall or surplus to the fixed target.
Select cell A3 and drag the fill handle down through the remaining weeks to carry each week's difference forward.

Use WPS Office Excel to Carry Weekly Shortfalls Forward with a Simple Formula
Open your weekly tracking sheet in WPS Office Excel, place the fixed weekly target in E1, and apply the carry-forward formulas in column A so each week's target adjusts automatically.
- 1. Open WPS Office Excel: Launch WPS Office and open the workbook that contains your weekly target and actual results.
- 2. Enter the fixed target: In cell E1, type the fixed weekly target value, such as 126.
- 3. Add the carry-forward formulas: In A2 enter =E1, then in A3 enter =$E$1+A2-B2.
- 4. Fill down and save: Drag the fill handle down column A, then save the workbook in .xlsx format.

Frequently Asked Questions
Why use $E$1 instead of E1 in the carry-forward formula?
The dollar signs create an absolute reference to cell E1, so when you fill the formula down, every row still adds the original fixed weekly target instead of shifting to E2, E3, and so on.
What should be entered in column B?
Column B should contain the actual result for each week. The formula subtracts column B from the current target plus the fixed target, so a shortfall increases the next week's target and a surplus reduces it.
Can I use a different cell for the fixed weekly target?
Yes. Replace both E1 references in the formula with the cell that contains your fixed target, and use that same cell in the first week's formula.
Does this formula work in both WPS Office and Microsoft Excel?
Yes. The formula syntax and absolute reference behavior are the same in WPS Office Excel and Microsoft Excel, so you can share .xlsx files without changing the formulas.




