How to Calculate a Running Total with Additions and Subtractions in WPS Office Excel
Question details
The user needs a running balance formula in WPS Office Excel where column C entries subtract from the previous balance and column D entries add to it, leaving the balance cell blank until an adjustment is entered.
- Product
- WPS Office Excel
- Device & OS
- not provided
- Scenario
- The user is building a spreadsheet register or ledger and wants each row to update a balance based on subtraction and addition columns.
- Observed behavior
- The balance column should subtract column C and add column D from the previous balance; it should stay blank when no entry exists in the current row.
Confirm that column C contains amounts to subtract, column D contains amounts to add, and column E is the running balance. Row 2 should contain the starting balance or previous balance so the first formula has a value to reference.
Use an IF Formula for a Blank-Aware Running Balance
Use this formula when you want the balance column to remain blank until an addition or subtraction is entered.
The IF function checks whether columns C and D are empty. If both are empty, the formula returns blank text; otherwise it takes the previous balance, subtracts column C, and adds column D.
Click cell E3, the first row where the running balance should appear.
Type =IF(SUM(C3:D3)=0,"",E2-C3+D3) into cell E3 and press Enter.
Drag the fill handle from the lower-right corner of E3 down through the remaining balance rows in column E.
Enter a subtraction in C3 and an addition in D3; confirm E3 updates from E2 and rows without entries remain blank.
Create a Running Balance in WPS Office Excel
WPS Office Excel lets you create a running total with the same formulas and cell references used in Microsoft Excel. Enter the IF formula, fill it down, and your balance column updates automatically while preserving Excel compatibility.
- 1. Open your workbook in WPS Office: Launch WPS Office Excel and open the workbook that contains columns C, D, and E.
- 2. Select the first balance cell: Click cell E3, the cell where the new running balance formula will start.
- 3. Enter the running total formula: Type =IF(SUM(C3:D3)=0,"",E2-C3+D3) and press Enter.
- 4. Fill the formula down: Drag the fill handle from cell E3 down to apply the formula to the remaining balance rows.
- 5. Verify the running balance: Enter test amounts in columns C and D to confirm the balance subtracts and adds correctly and blank rows stay blank.
Frequently Asked Questions
Can I use this formula if my subtraction and addition columns are not C and D?
Yes. Replace C3 and D3 with your actual subtraction and addition cells. The previous balance reference E2 must point to the cell directly above your first formula.
Why does the formula use SUM(C3:D3)=0 instead of checking each cell?
SUM returns 0 when both cells are empty or when values total zero, efficiently keeping the row blank until an entry is made. If a legitimate adjustment can total zero, use =IF(AND(C3="",D3=""),"",E2-C3+D3).
How do I start the running total if my first balance is not in row 2?
Put your starting balance in the cell immediately above the first formula or change E2 to the cell containing the previous balance, then fill down.
Will the formula work in Microsoft Excel?
Yes. The formula uses standard Excel functions and cell references, so the workbook remains compatible between WPS Office and Microsoft Excel.
