Skip to content

Instantly share code, notes, and snippets.

@ncalm
Last active June 15, 2023 09:42
  • Star 0 You must be signed in to star a gist
  • Fork 1 You must be signed in to fork a gist
Star You must be signed in to star a gist
Save ncalm/40f251c07ab6609f86a92789210fd3de to your computer and use it in GitHub Desktop.
This Excel lambda function creates an array of actuals vs forecast for quality control of time series data
/*
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
)
)
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment