With Excel 2016, 5 new functions have been added: FORECAST.LINEAR FORECAST.ETS FORECAST.ETS.CONFINT FORECAST.ETS.SEASONALITY FORECAST.ETS.STAT (and the function FORECAST has been labeled obsolete, but is still supported) see: https://support.office.com/en-us/article/Forecasting-functions-897a2fe9-6595-4680-a0b0-93e0308d5f6e?ui=en-US&rs=en-US&ad=US#_forecast.ets
Further studies of publications and literature about Triple Exponential Smoothing (AKA Holt-Winters method) shows that there are 2 methods, depending on the seasonal influences. If the seasonal influence is absolute (e.g. in July 10 extra icecreams are sold), the additive method is used, if the seasonal influence is relative (e.g. in July 10% extra icecreams are sold), the multiplicative method is used. For the additive there is a generally accepted method to calculate prediction intervals, often -but incorrectly- called confidence intervals. As the interval is calculated for predicted values, not for actual observations, it is not possible to calculate a proper confidence interval. For the multiplicative method I haven't found a method that is recommended yet. Excel 2016 uses the additive method. I intend to add both methods to Calc.
Created attachment 121190 [details] some intermediate results Calc document contains 4 datasets with observations, 3 taken from forecasting examples and 1 (the sinus) to modify various parameters of the observations for testing. Each dataset has 3 ETS-forecasts: Calc additive, Calc multiplicative and Excel2016 additive, as well as the statistics for these 3 forecasts. The RMSE (Root Mean Squared Error) is used for optimising the forecast. The prediction interval only shows for Excel2016, as I have not yet implemented this in Calc.
Created attachment 121384 [details] some intermediate results Now with prediction interval calculations.
Created attachment 121750 [details] some results and comparisons Finally, the code is complete. Now retest every aspect and behaviour and add a unittest...
Winfried Donkers committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=f336f63da900d76c2bf6e5690f1c8a7bd15a0aa2 tdf#94635 Add FORECAST.ETS functions to Calc It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Winfried, thanks a lot!
(In reply to Gerry from comment #6) > Winfried, thanks a lot! You're welcome. I just think I haven't updated you wiki with Calc-Excel comparisons. (Note that Excel uses slightly different function names because Excel only supports 1 method (additive) whereas Calc supports both additive and multiplicative methods with triple exponential smoothing. Also, The Excel FORECAST.ETS.SEASONALITY is not really necessary as a separate function in Calc (though it is available) because this value can be returned from the FORECAST.ETS.STAT.ADD/MULT functions in Calc.)
Winfried Donkers committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=c788f63726df3340e787bb92477d7ad31e7bc952 follow up of tdf#94635 It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Winfried Donkers committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=08306201bc88b2efe915e27799b44aaf00a3db7e tdf#94635 synchronise argument labels in function wizard with It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Eike Rathke committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=0aa29f32f9c39f598f43cdf3664b89d6cb151b17 prefix domain namespace to FORECAST.* functions for ODFF, tdf#94635 follow-up It will be available in 5.2.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
(In reply to Commit Notification from comment #10) > Eike Rathke committed a patch related to this issue. > prefix domain namespace to FORECAST.* functions for ODFF, tdf#94635 follow-up @Eike: I noticed the problem last Sunday whilst opening an xlsx-document but you were to fast to let me fix it ;-) Thanks!
Created attachment 127341 [details] some examples and comparisons Updated document reflecting current function names.
Winfried Donkers committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=ad707a3dafd020949cfd94098dc51c69466dafb8 tdf#94635 follow up; correct handling of double x-values in case of It will be available in 5.3.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Winfried Donkers committed a patch related to this issue. It has been pushed to "master": http://cgit.freedesktop.org/libreoffice/core/commit/?id=fb790880eb11552b2e7d1dcf6c09d2663712290f tdf#94635 follow up; handle linear data properly when samples in period It will be available in 5.3.0. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
@Winfried: Should we cherry-pick/backport the last two patches to the 5-2 branch?
(In reply to Eike Rathke from comment #15) > @Winfried: > Should we cherry-pick/backport the last two patches to the 5-2 branch? @Eike: Yes we should, they fix bugs and are not enhancements.
Winfried Donkers committed a patch related to this issue. It has been pushed to "libreoffice-5-2": http://cgit.freedesktop.org/libreoffice/core/commit/?id=f617d9466632f656811474cd49311e13ab950753&h=libreoffice-5-2 tdf#94635 follow up; correct handling of double x-values in case of It will be available in 5.2.4. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.
Winfried Donkers committed a patch related to this issue. It has been pushed to "libreoffice-5-2": http://cgit.freedesktop.org/libreoffice/core/commit/?id=aaa38fd34faab3108245f8fa8c13642919ff0c95&h=libreoffice-5-2 tdf#94635 follow up; handle linear data properly when samples in period It will be available in 5.2.4. The patch should be included in the daily builds available at http://dev-builds.libreoffice.org/daily/ in the next 24-48 hours. More information about daily builds can be found at: http://wiki.documentfoundation.org/Testing_Daily_Builds Affected users are encouraged to test the fix and report feedback.