This Excel lambda function creates an array of actuals vs forecast for quality control of time series data

excel-lambda-FORECAST.ETS.COMPARE.txt

/*
FORECAST.ETS.COMPARE

Creates an array enabling comparison of actuals vs forecasted data

Inputs:
  Required:
  - data - this is a two-column range or array of data where one of the columns contains dates and one of the columns contains the values to be used as the basis for the forecast
  - dates_in_column - is an integer, either 1 or 2, telling the function which of the two selected columns in data contains the dates
  - values_in_column - is an integer, either 1 or 2, telling the function which of the two selected columns in data contains the values to be used as the basis for the forecast
  - forecast_last_x_values - is an integer which is smaller than the total number of rows in data. This value is the number of periods at the end of the selected data range that you would like to create forecasts for based on all the other rows not in the last X rows
  
  Optional:
  - [seasonality] - The default value of 1 means Excel detects seasonality automatically for the forecast and uses positive, whole numbers for the length of the seasonal pattern. 0 indicates no seasonality, meaning the prediction will be linear. Positive whole numbers will indicate to the algorithm to use patterns of this length as the seasonality. For any other value, FORECAST.ETS will return the #NUM! error. Maximum supported seasonality is 8,760 (number of hours in a year). Any seasonality above that number will result in the #NUM! error.
  - [data_completion] - Although the timeline requires a constant step between data points, FORECAST.ETS supports up to 30% missing data, and will automatically adjust for it. 0 will indicate the algorithm to account for missing points as zeros. The default value of 1 will account for missing points by completing them to be the average of the neighboring points
  - [aggregation] - Although the timeline requires a constant step between data points, FORECAST.ETS will aggregate multiple points which have the same time stamp. The aggregation parameter is a numeric value indicating which method will be used to aggregate several values with the same time stamp. The default value of 0 will use AVERAGE, while other options are SUM, COUNT, COUNTA, MIN, MAX, MEDIAN
	
Returns:
	An array with ROWS(data)+1 rows and COLUMNS(data)+3 columns, with header {"dates","actuals","forecast","variance","variance %"}
	
  The forecast column contains the same values as the "actuals" column, except for the last forecast_last_x_values rows, which 
  contain values calculated by FORECAST.ETS
  

*/


FORECAST.ETS.COMPARE =LAMBDA(data,dates_in_column,values_in_column,forecast_last_x_values,[seasonality],[data_completion],[aggregation],
  LET(
    _data,data,
    _data_rows,ROWS(_data),
    _data_cols,COLUMNS(_data),
    _dates,INDEX(_data,,dates_in_column),
    _values,INDEX(_data,,values_in_column),
    _train_end_row,_data_rows-forecast_last_x_values,
    _dates_train,INDEX(_dates,1,1):INDEX(_dates,_train_end_row,1),
    _values_train,INDEX(_values,1,1):INDEX(_values,_train_end_row,1),
    _header,{"dates","actuals","forecast","variance","variance %"},
    _array,
    MAKEARRAY(
      _data_rows+1,
      _data_cols+3,
      LAMBDA(r,c,
        LET(
          _row_date,INDEX(_dates,r-1,1),
          _row_value,INDEX(_values,r-1,1),
          _forecast,FORECAST.ETS(
                      _row_date,
                      _values_train,
                      _dates_train,
                      IF(ISOMITTED(seasonality),1,seasonality),
                      IF(ISOMITTED(data_completion),1,data_completion),
                      IF(ISOMITTED(aggregation),1,aggregation)
                    ),
          _var,_forecast-_row_value,
          _var_pct,_forecast/_row_value-1,
          IFS(
            r=1,INDEX(_header,1,c),
            c=1,_row_date,
            c=2,_row_value,
            r-1<=_train_end_row,CHOOSE(c-2,_row_value,0,0),
            r-1>_train_end_row,CHOOSE(c-2,_forecast,_var,_var_pct),
            TRUE,NA()
          )
        )
      )
    ),
    _array
  )
)