Fix an Excel Running Balance Formula Returning #VALUE! in WPS Office Spreadsheets
Question details
The user needs to troubleshoot a check-register running balance formula in WPS Office Spreadsheets that returns a #VALUE! error when debit and credit entries are referenced.
- Product
- WPS Office Spreadsheets
- Device & OS
- not provided
- Scenario
- Creating or maintaining a check-register running balance where debit and credit entries should update the balance automatically.
- Observed behavior
- The running balance formula returns a #VALUE! error when referencing debit or credit cells because those cells contain text, spaces, currency symbols, or errors instead of numbers.
Before editing the formula, select the debit and credit columns and check the Number Format on the Home tab. Remove any spaces, currency symbols, or text-like entries from cells that should contain only numbers.
Use =G2+E3-F3 After Confirming Debit and Credit Cells Are Numeric
Use this for a simple check-register layout when you want a row-by-row running balance in column G.
This formula adds the current debit entry and subtracts the current credit entry from the previous balance. The #VALUE! error typically appears because at least one referenced E or F cell is text, has a hidden space, or contains an error.
In WPS Office Spreadsheets, open the workbook and select columns E and F, or the columns holding debits and credits. On the Home tab, open the Number Format dropdown and confirm the cells use Number or General rather than Text.
Inspect the selected cells for currency symbols typed inside the cell, leading apostrophes, spaces, or formula errors. Delete any non-numeric characters so only numbers remain.
Click cell G2 and type the opening balance amount, such as 1000, then press Enter.
Click cell G3, type =G2+E3-F3, and press Enter. With G3 selected, drag the fill handle down column G to copy the formula to the remaining rows.
Use SCAN or SUMIFS for a Dynamic Running Balance
Use this when you want one formula to return the balance for the entire column or avoid dragging the fill handle down.
Fix Running Balance #VALUE! Errors with WPS Office Spreadsheets
Open your Excel check-register workbook in WPS Office Spreadsheets, confirm the debit and credit columns contain numeric values, enter =G2+E3-F3, and fill the formula down to clear the #VALUE! error and keep your running balance accurate.
- 1. Open the Excel workbook: Launch WPS Office Spreadsheets, click Menu > Open, and select the .xlsx check-register workbook.
- 2. Set debit and credit columns to Number: Select columns E and F, go to the Home tab, open the Number Format dropdown, and choose Number.
- 3. Enter the opening balance: Click cell G2 and type the opening balance amount, then press Enter.
- 4. Enter and fill the formula: Click cell G3, type =G2+E3-F3, press Enter, then drag the fill handle down through the last register row.
- 5. Confirm the result: Check that column G now shows running balances instead of #VALUE!, then save the workbook.
Frequently Asked Questions
Why does a running balance formula return #VALUE! when the cells look like numbers?
The cells may actually be text, or contain leading apostrophes, spaces, currency symbols, or errors. Select the debit and credit columns, open the Number Format dropdown on the Home tab, and use =ISNUMBER(E3) to test a cell. If it returns FALSE, remove the text characters or re-enter the value as a number.
What formula should I use for a simple check-register running balance in WPS Office?
Place the opening balance in G2. In G3 enter =G2+E3-F3 and press Enter. Then drag the fill handle down column G so each row adds the current debit and subtracts the current credit from the previous balance.
Can I calculate the running balance for the entire column without dragging the formula down?
Yes. If your version supports dynamic arrays, enter =SCAN(0,credits-debits,LAMBDA(x,y,x+y)) in the first balance cell. You can also use =SUMIFS(credits,date,"<="&date)-SUMIFS(debits,date,"<="&date) when the data includes a date column.
Does WPS Office support Excel running balance workbooks and formulas?
Yes. WPS Office Spreadsheets opens and saves .xlsx Excel workbooks and supports formulas like =G2+E3-F3, SUMIFS, and SCAN in compatible versions. You can open the file from Menu > Open and edit the formula directly.
