How to Fix the #NUM! Error in FORECAST.ETS and FORECAST.ETS.CONFINT in Spreadsheet
Question details
The user encounters a #NUM! error when attempting to calculate predicted values using the FORECAST.ETS or FORECAST.ETS.CONFINT formulas.
- Product
- Spreadsheet
- Device & OS
- not provided
- Scenario
- Using forecasting functions on historical date data to predict future trends.
- Observed behavior
- The formula returns a #NUM! error instead of the forecasted numeric value, typically due to ambiguous dates or chronological timeline mismatches.
Ensure your historical dataset contains at least two numerical data points and that your historical timeline is formatted as actual dates rather than text.
Ensure the Target Date is After the Historical Timeline
The most common cause of the #NUM! error in forecasting formulas is setting a target date that falls before or within your existing historical data timeline.
Forecasting algorithms require the target date (the date you want to predict a value for) to be chronologically later than the final date in your historical dataset. If it is earlier, the formula cannot logically predict the future, resulting in a #NUM! error.
Select the cell containing your FORECAST.ETS or FORECAST.ETS.CONFINT formula.
Identify the latest chronological date in your historical timeline array (for example, the maximum date in the range $D$5:$D$16).
Click on the cell referencing your target date (e.g., D18) and change it to a date that occurs after the latest date found in your historical timeline.
Press Enter to recalculate the formula and confirm that the #NUM! error has been replaced by the forecasted value.
Convert Ambiguous Text Dates into Complete Date Serial Numbers
Ambiguous dates (like '21-Jul' or custom fiscal year labels) can be misinterpreted by the spreadsheet as text or the wrong year, breaking the chronological sequence required by the formula.
Use WPS Spreadsheet for Flawless Data Forecasting
WPS Spreadsheet fully supports advanced statistical formulas like FORECAST.ETS and FORECAST.ETS.CONFINT, making it incredibly easy to predict future trends based on historical data without encountering compatibility issues.
- 1. Open your dataset: Launch WPS Spreadsheet and open the document containing your historical data.
- 2. Insert the formula: Select a blank cell, type =FORECAST.ETS(, and use the tooltip guide to select your target date, values, and timeline ranges.
- 3. Format timeline dates: Ensure your timeline dates are formatted correctly by using the 'Format Cells' dialog found in the Home ribbon.
- 4. Calculate the forecast: Press Enter to instantly process the algorithm and calculate your accurately predicted future value.
Frequently Asked Questions
Why does my FORECAST.ETS function return a #NUM! error?
This error typically occurs if your forecast target date is chronologically before or equal to the end of your historical timeline. It can also happen if the dates in your timeline are ambiguous, inconsistent, or stored as text instead of valid date serial numbers.
How do I fix ambiguous dates in my spreadsheet timeline?
Select your timeline cells, open the Format Cells dialog by right-clicking, and choose a full date format (e.g., MM/DD/YYYY). This reveals the underlying serial numbers and highlights any dates that were mistakenly stored as text so you can correct them.
Does FORECAST.ETS require the historical timeline to be sorted?
While the FORECAST.ETS function can handle an unsorted timeline, the dates must still represent a consistent step or interval. Additionally, the target date you are forecasting must still be strictly after the latest chronological historical date to prevent a #NUM! error.
Can fiscal year labels cause #NUM! errors in forecasting?
Yes. If fiscal years are entered as text labels (e.g., 'FY2021') instead of actual chronological date values (e.g., '06/30/2021'), the forecasting function cannot interpret the timeline correctly. Always use true date values for your timeline array.
