ExcelFunctions.net

Search Site:

In Excel 2016, Microsoft have introduced the following new built-in statistical functions. Note that these functions are not available in Excel 2016 for Mac.

Statistical Functions | |
---|---|

FORECAST.ETS | Uses an exponential smoothing algorithm to predict a future value on a timeline, based on a series of existing values ## Forecast.Ets Function ExampleIf you have a series of monthly earnings, the Forecast.Ets function can be used to predict the earnings for the next month, as shown in the following spreadsheet: |

FORECAST.ETS.CONFINT | Returns a confidence interval for a forecast value at a specified target date. ## Forecast.Ets.Confint Function ExampleThe spreadsheet below shows a series of monthly earnings between Aug-2016 & Apr-2017, and the Forecast.Ets function has been used in cell G11, to predict the earnings for May 2017. The Forecast.Ets.Confint function is used in cell C13 of the spreadsheet, to calculate a confidence interval for the forecasted value: |

FORECAST.ETS.SEASONALITY | Returns the length of the repetitive pattern Excel detects for a specified time series. ## Forecast.Ets.Seasonality Function ExampleIf you have a series of monthly earnings, the Forecast.Ets.Seasonality function can be used to detect the length of a seasonal pattern, as shown in the following spreadsheet: |

FORECAST.ETS.STAT | Returns a statistical value relating to a time series forecasting. ## Forecast.Ets.Stat Function ExampleThe spreadsheet below shows a series of monthly earnings between Aug-2016 & Apr-2017, and the Forecast.Ets function has been used in cell G11, to predict the earnings for May 2017. The Forecast.Ets.Stat function is used in cell C13 of the spreadsheet, to return the Alpha parameter of the ETS algorithm: |

FORECAST.LINEAR | Predicts a future point on a linear trend line fitted to a supplied set of x- and y- values. ## Forecast.Linear Function ExampleIn the following spreadsheet, the Forecast.Linear function is used to predict the value of the trend line through the given x- and y- values in cells F2-G7, at the value x=7: |