WPS Spreadsheet Formula to Use Budgeted or Actual Revenue and Expenses
Question details
The user needs a formula that uses actual revenue and expenses when available and budgeted values when actual amounts are missing.
- Product
- WPS Spreadsheet
- Device & OS
- not provided
- Scenario
- An ending cash balance worksheet contains starting cash, budgeted revenue, actual revenue, budgeted expenses, actual expenses, and additional payments.
- Observed behavior
- The existing calculation excludes budgeted revenue when actual revenue is blank, producing $97,000 instead of the expected $102,000.
Confirm which cells contain starting cash, budgeted revenue, actual revenue, budgeted expenses, actual expenses, and other payments. Check whether missing actual values are stored as zero or as truly empty cells, because the formula's tests should match the workbook.
Use a formula that falls back to budgeted values when actuals are zero
Use this compact formula when missing actual revenue or expenses are represented by zero or blank cells.
The formula adds budgeted revenue only when actual revenue is zero, otherwise it adds actual revenue. It subtracts budgeted expenses when actual expenses are zero and subtracts actual expenses when they are present.
In WPS Spreadsheet, click the cell where the ending cash balance should appear.
Type =C4+C5*(C6=0)+C6-C7*(C8=0)-C8-C9, then press Enter. In this layout, C4 is starting cash, C5 is budgeted revenue, C6 is actual revenue, C7 is budgeted expenses, C8 is actual expenses, and C9 is the additional payment.
Check that the ending balance includes C5 when C6 is blank or zero and includes C7 when C8 is blank or zero. With the example values, the result should be $102,000.
Use an IF formula for explicit blank and actual-value checks
Use this option when the worksheet needs clearly defined blank-cell logic or different behavior for zero values.
Calculate Budgeted and Actual Cash Flows in WPS Spreadsheet
WPS Spreadsheet lets you enter the fallback formula, test blank or zero actual values, and calculate an accurate ending cash balance in a familiar spreadsheet interface. It supports common Excel workbook formats for easy migration.
- 1. Open a spreadsheet: Launch WPS Office, choose Spreadsheet, and open the workbook containing the budget and actual figures.
- 2. Map the input cells: Identify the cells for starting cash, budgeted revenue, actual revenue, budgeted expenses, actual expenses, and other payments.
- 3. Enter the formula: Select the ending balance cell and enter =C4+C5*(C6=0)+C6-C7*(C8=0)-C8-C9.
- 4. Check missing-value logic: Verify whether blank or zero actual values trigger the intended budgeted revenue and expense fallbacks.
Frequently Asked Questions
How does the WPS Spreadsheet formula choose between actual and budgeted revenue?
The expression C5*(C6=0)+C6 adds budgeted revenue in C5 when actual revenue in C6 is zero or treated as blank; otherwise, it adds the actual revenue.
How does the formula choose between actual and budgeted expenses?
The expression -C7*(C8=0)-C8 subtracts budgeted expenses in C7 when actual expenses in C8 are zero or blank; otherwise, it subtracts actual expenses.
Why does my ending balance omit budgeted revenue when actual revenue is empty?
Your original formula likely subtracts expenses correctly but does not include a fallback term for budgeted revenue. Add C5*(C6=0) before adding C6, or use an IF formula that tests whether C6 is blank.
What should I change if missing values are empty cells instead of zeros?
Test the actual cells explicitly with C6="" and C8="" using IF or AND. If WPS Spreadsheet treats the cells as zero in the existing formula, the compact zero-test formula can still work; otherwise, use explicit blank checks.
Can I use this formula in an Excel workbook opened in WPS Spreadsheet?
Yes. Enter the formula in a compatible XLS or XLSX workbook, confirm that the cell references match your layout, and save the file in its original format if required.
