How to Use Excel Formulas to Switch Between Actuals and Forecasts by Month
Question details
The user needs a method to calculate a combined total by dynamically switching between actual values for elapsed months and forecast values for future months in a financial workbook.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Creating a financial report or budget tracker that combines actual and forecast data depending on the current or selected month, without overriding original forecast inputs.
- Observed behavior
- The goal is to automatically output actual numbers where available, and fall back to original forecast numbers for future months to maintain accurate year-to-date and projected totals.
Ensure your financial data is structured correctly, ideally with clearly labeled 'Actual' and 'Forecast' columns adjacent to each other for every respective month.
Use an IF Formula for Dynamic Column Selection
The simplest and most direct method to switch between actuals and forecasts is writing an IF function that checks if an actual value exists for a given month.
By utilizing the IF function, Excel can verify whether the 'Actual' cell is empty. If it is empty, the formula retrieves the projected 'Forecast' value. If data has been entered into the 'Actual' cell, the formula prioritizes that actual amount.
Locate the cells containing your actual and forecast data. For example, assume BS5 is the Forecast for May and BT5 is the Actual for May.
In your total or summary cell, enter the formula: =IF(BT5="",BS5,BT5). This logic dictates that if the Actual cell (BT5) is blank, use the Forecast (BS5); otherwise, use the Actual (BT5).
To add totals across several months dynamically, chain the IF formulas using a plus sign. For example: =IF(BT5="",BS5,BT5)+IF(BW5="",BV5,BW5).
Press Enter to calculate the result, then click and drag the fill handle at the bottom-right of the cell to copy the formula down to other rows in your financial report.

Use a Structured Table and PivotTable for Larger Datasets
For extensive financial data, restructuring your data into a flat table and utilizing PivotTables or Power Query offers better scalability and easier dynamic reporting.
Easily Manage Financial Actuals and Forecasts in WPS Spreadsheet
WPS Spreadsheet provides robust formula support and advanced PivotTable functionalities to effortlessly manage dynamic financial reports, ensuring precise tracking of actuals versus forecasts.
- 1. Open your financial workbook: Launch WPS Spreadsheet and open your existing financial report containing the actual and forecast data.
- 2. Input the IF formula: Select the target summary cell and enter your conditional formula, such as =IF(BT5="",BS5,BT5)+IF(BW5="",BV5,BW5).
- 3. Create a PivotTable for advanced reports: For structured data, go to the Insert tab, select PivotTable, and drag your Month, Resource, and Amount fields to instantly generate a dynamic summary.

Frequently Asked Questions
How do I dynamically switch forecasts based on a dropdown month selection?
You can use an IF function combined with the MONTH function or an INDEX/MATCH setup tied to a data validation dropdown. By comparing the month header to the selected dropdown month, the formula can automatically pull actuals for months prior to the selection and forecasts for months after.
What if my actuals show zero instead of blank?
If your actual data contains zeros instead of blanks, update the IF condition to check for a zero value. For example, use =IF(BT5=0,BS5,BT5) so that the formula falls back to the forecast amount when the actual reported amount is exactly zero.
Can I use the IFS function instead of nested IFs for this?
Yes, the IFS function is excellent for handling multiple conditions sequentially without nesting. However, for a simple Actual vs. Forecast check on a per-month basis, a standard IF function is usually sufficient and highly compatible with older spreadsheet versions.
Why isn't my IF formula updating when I add new actuals?
Ensure that your workbook calculation options are set to 'Automatic' in the Formulas tab. Additionally, check that the cells containing actuals aren't formatted as text or contain hidden space characters, which would prevent the ="" (blank) condition from evaluating correctly.




