FORECAST.ETS.STAT in Excel

This error occurs when the timeline parameter is not sorted chronologically, contains text or empty cells, or doesn't have consistent time steps between values. Excel requires timeline dates to be in strict ascending order (oldest to newest) with relatively uniform intervals. The error also appears when statistic_type is not a number between 1 and 8, or when parameters like data_completion (must be 0 or 1) or aggregation (must be 1-7) are outside valid ranges. Additionally, if the values range contains text, logical values, or errors instead of numbers, this error will occur.

**Systematic troubleshooting steps:** 1. Verify timeline is sorted: Select timeline range and use Data tab > Sort to arrange dates in ascending chronological order from oldest to newest. 2. Check for non-date values: Use this helper formula to identify problem cells: =IF(ISNUMBER(A2), "OK", "ERROR") in a column next to timeline. Look for FALSE values indicating text or non-numeric entries. 3. Remove blank cells: Filter timeline to show blanks, then either delete those rows or fill with appropriate dates. Gaps in timeline cause inconsistent step detection. 4. Validate statistic_type: Ensure you're using an integer between 1 and 8. Common mistake is using decimal values or text. Use formula validation: =IF(AND(statistic_type>=1, statistic_type<=8), "Valid", "Must be 1-8"). 5. Check parameter ranges: Verify data_completion is 0 or 1 (not blank or other values), and aggregation is 1 through 7. 6. Verify timeline consistency: Create helper column to check intervals: =DAYS(A3,A2). Look for major deviations from the typical step size. 7. Use error handling in production: =IFERROR(FORECAST.ETS.STAT(...), "Timeline not sorted or contains invalid values") **Prevention strategy:** Before using FORECAST.ETS.STAT, create a data validation section with formulas to check prerequisites: - Timeline sorted: =IF(AND(A3:A100>A2:A99), "Pass", "FAIL: Sort timeline") - Values numeric: =IF(SUMPRODUCT(--ISNUMBER(B2:B100))=COUNTA(B2:B100), "Pass", "FAIL: Non-numeric values") - Statistic type valid: =IF(AND(stat_type>=1, stat_type<=8, stat_type=INT(stat_type)), "Pass", "FAIL: Use integer 1-8") This error represents approximately 45% of all FORECAST.ETS.STAT errors and is almost always caused by data quality issues rather than formula mistakes.

This numeric error indicates insufficient data points for the requested analysis or invalid statistic_type parameter. Excel requires at least 2 complete seasonal cycles for reliable statistics when seasonality is specified. For example, with seasonality=12 (monthly with yearly patterns), you need minimum 24 data points. For automatic seasonality detection (seasonality=1), you need at least 4 data points, but realistically 8+ for meaningful results. The error also occurs when you specify a seasonality value larger than one-third of your total data points—for example, seasonality=15 with only 30 data points. Additionally, if the timeline contains duplicate dates without the aggregation parameter properly handling them, this error may appear.

**Complete resolution process:** 1. Count available data points: =COUNTA(B2:B100) to determine how many valid observations you have. 2. Calculate minimum required: For manual seasonality, minimum is seasonality × 2. For seasonality=12, you need at least 24 points. 3. Verify statistic_type is valid: Must be integer between 1 and 8. Use: =IF(statistic_type=INT(statistic_type), "OK", "ERROR: Must be whole number"). 4. Check seasonality ratio: Seasonality cannot exceed ⅓ of data points. Formula check: =IF(seasonality<=(COUNTA(values)/3), "Valid", "ERROR: Reduce seasonality"). 5. Handle insufficient data: If you have too few points, reduce seasonality or use seasonality=0 (no seasonal component). Alternative: collect more historical data before attempting statistical validation. 6. Test for duplicate dates: Create helper column: =COUNTIF($A$2:$A$100, A2) to identify duplicates. If duplicates exist, ensure aggregation parameter is set to handle them (default 1=AVERAGE works for most cases). 7. Verify timeline intervals: Extremely irregular spacing can cause numeric errors. Check that most intervals are similar using: =STDEV(time_differences)/AVERAGE(time_differences). Coefficient of variation above 0.5 indicates problematic irregularity. 8. Use data adequacy check: Before calculating statistics, validate: =IF(COUNTA(values)>=(seasonality*2), "Proceed", CONCATENATE("Need ", seasonality*2-COUNTA(values), " more data points")) **Prevention approach:** Build data adequacy checks into forecast dashboards. Create a dashboard section that displays: - Data points available: =COUNTA(B2:B100) - Data points required: =seasonality_value*2 - Status: =IF(available>=required, "✓ Sufficient", "✗ Need more data") This prevents attempting analysis on insufficient data. For borderline cases (barely enough data), consider increasing data collection period or reducing seasonality assumption to simpler model. This error accounts for approximately 30% of FORECAST.ETS.STAT errors and primarily occurs when users attempt sophisticated seasonal analysis without adequate historical data.

FORECAST.ETS.STAT is not available in your Excel version (requires Excel 2016 or later, not available in Google Sheets or Excel 2013 and earlier). This error also appears when the values and timeline ranges are completely empty with no data at all, or when the ranges have different lengths—for example, timeline covers A2:A100 but values covers B2:B50. Less commonly, it occurs when one or both range references have been deleted or point to external workbooks that are closed. In some cases, if the workbook is in compatibility mode (saved as .xls instead of .xlsx), this function may not be recognized even in Excel 2016+.

**Comprehensive diagnostic steps:** 1. Verify Excel version: File menu > Account > About Excel. Look for version number. FORECAST.ETS.STAT requires Excel 2016, 2019, 2021, or Microsoft 365. Excel 2013 and earlier cannot use this function. 2. Update Excel if needed: If using older version, upgrade to Excel 2016 or later, or use Microsoft 365 subscription for continuous updates. 3. Check for empty ranges: Ensure both values and timeline contain data. Use: =IF(COUNTA(B2:B100)>0, "Has data", "Empty range") to verify. 4. Verify range lengths match: =IF(COUNTA(A2:A100)=COUNTA(B2:B100), "Match", "MISMATCH") to ensure timeline and values have identical sizes. 5. Check for deleted references: If formula shows #REF! within the FORECAST.ETS.STAT call, ranges have been deleted. Update references to point to correct cells. 6. Verify file format: Save As > Excel Workbook (.xlsx) to ensure you're not in compatibility mode. File format .xls (Excel 97-2003) doesn't support new functions. 7. Check external references: If timeline or values reference another workbook, ensure that workbook is open. Close external references by copying data into current workbook. 8. Google Sheets compatibility: This function does NOT work in Google Sheets. If you need similar functionality in Sheets, you must use third-party add-ons or export to Excel. **Platform alternatives:** Excel 2016+: Use FORECAST.ETS.STAT as documented Excel 2013 and earlier: No direct equivalent—use manual calculation of forecast errors or upgrade Excel Google Sheets: No equivalent function—export to Excel or use add-ons Excel Online: Fully supported with identical syntax to desktop Excel This error represents approximately 15% of FORECAST.ETS.STAT errors and is primarily a platform compatibility issue rather than data or formula problem.

High accuracy metrics don't indicate an error—they reveal that forecast quality is genuinely poor. This happens when data has irregular patterns that don't fit exponential smoothing assumptions, insufficient historical data for the specified seasonality (barely meeting minimums), structural breaks in the data where business conditions changed dramatically (COVID-19 impact, merger, business model pivot), inappropriate seasonality setting (forcing 12-month seasonality on data that's actually quarterly), significant outliers distorting the model, or data that's simply too random to forecast reliably. High MASE (above 1.5) means the ETS forecast performs worse than just using the last known value, questioning whether sophisticated forecasting adds any value.

**Diagnostic and improvement process:** 1. Check data sufficiency: Verify you have at least 2 complete seasonal cycles. More data generally improves accuracy. Use: =COUNTA(values)/seasonality to calculate how many cycles you have. 2. Validate seasonality assumption: Use FORECAST.ETS.SEASONALITY(values, timeline) to see what pattern Excel automatically detects. If it differs from your manual specification, investigate why. Your business assumption may not match data reality. 3. Identify structural breaks: Plot your data in a line chart and look for obvious breaks where patterns changed dramatically. If found, split data and only forecast using post-break periods. For example, use only post-pandemic data if COVID caused fundamental business model change. 4. Check for outliers: Calculate RMSE/MAE ratio using: =FORECAST.ETS.STAT(values,timeline,7)/FORECAST.ETS.STAT(values,timeline,6). Ratios above 2.0 indicate outliers are distorting the model. Investigate and potentially remove or adjust extreme values. 5. Test data_completion parameter: Try both 0 (treat missing as zero) and 1 (interpolate) to see which produces better statistics. Incorrect handling of gaps can significantly degrade accuracy. 6. Visualize fit: Create a chart comparing actuals vs. FORECAST.ETS predictions for historical periods. Visual inspection often reveals why accuracy is poor—perhaps seasonality assumption is wrong or trend isn't being captured. 7. Consider if data is forecastable: Some business metrics are genuinely random or driven by external factors not captured in historical patterns. MASE consistently above 1.5 suggests ETS may not be appropriate. Try FORECAST.LINEAR instead, or accept that this metric requires alternative approaches like driver-based forecasting. 8. Segment the data: Rather than forecasting all products/regions together, segment into groups with similar patterns and forecast separately. Aggregated data often shows poor forecastability even when components are predictable. **Interpretation guidelines:** MASE less than 0.8: Excellent—forecast significantly outperforms naive MASE 0.8-1.0: Good—forecast reliably better than naive MASE 1.0-1.5: Moderate—forecast marginally better, use with caution MASE greater than 1.5: Poor—forecast worse than naive, reconsider approach SMAPE less than 10%: Excellent accuracy SMAPE 10-20%: Good accuracy for business planning SMAPE 20-40%: Moderate accuracy, require wide confidence intervals SMAPE greater than 50%: Poor accuracy, likely unusable for decisions This isn't technically an error but rather information that your data may not be suitable for exponential smoothing forecasting. Represents about 5% of FORECAST.ETS.STAT concerns.