Forecast.ETS

davedec

New Member
Joined
Jul 15, 2013
Messages
11
Hi there, I have a basic time series data with dates and returned results (weights of animals).

The capture is on a monthly basis, however the capture is not always exactly the same period between periods (i.e. one capture point maybe on the 15th of one month and the 18th of another month)

In order to forecast animal weights in the future my approach has been to interpolate the time series to a daily basis then use the forecast.ets function based on the history of capture points, and future dates to provide a forecast.

I am striking a couple of issues:

1. The need to take the time series to a daily basis is due to irregular data capture in points in time
2. Using the forecast.ets function to provide a forecast on a daily basis seems to be resource heavy and slow
3. Does anyone know if it is possible to change to arrangement of the fx to flip the forecast question around. i.e. instead of providing a history of dates and weights and asking the weight for a future date, could it be asked based on a history of dates and weights at what date would the animal be a certain weight?

Any thoughts or suggestions would be greatly appreciated

Regards

Dave

Apologies there seems to be a difficulty capturing the Fx in this range - forecast is in yellow

EID951 000310440436
DayHist / ForecastDate / Weights -->ScanLast WeightNext WeightLast DateNext DateETS
454Hist29-Nov-201931131131129-Nov-201929-Nov-2019311.00
455Hist30-Nov-2019 31134129-Nov-201909-Jan-2020311.73
456Hist01-Dec-2019 31134129-Nov-201909-Jan-2020312.46
457Hist02-Dec-2019 31134129-Nov-201909-Jan-2020313.20
458Hist03-Dec-2019 31134129-Nov-201909-Jan-2020313.93
459Hist04-Dec-2019 31134129-Nov-201909-Jan-2020314.66
460Hist05-Dec-2019 31134129-Nov-201909-Jan-2020315.39
461Hist06-Dec-2019 31134129-Nov-201909-Jan-2020316.12
462Hist07-Dec-2019 31134129-Nov-201909-Jan-2020316.85
463Hist08-Dec-2019 31134129-Nov-201909-Jan-2020317.59
464Hist09-Dec-2019 31134129-Nov-201909-Jan-2020318.32
465Hist10-Dec-2019 31134129-Nov-201909-Jan-2020319.05
466Hist11-Dec-2019 31134129-Nov-201909-Jan-2020319.78
467Hist12-Dec-2019 31134129-Nov-201909-Jan-2020320.51
468Hist13-Dec-2019 31134129-Nov-201909-Jan-2020321.24
469Hist14-Dec-2019 31134129-Nov-201909-Jan-2020321.98
470Hist15-Dec-2019 31134129-Nov-201909-Jan-2020322.71
471Hist16-Dec-2019 31134129-Nov-201909-Jan-2020323.44
472Hist17-Dec-2019 31134129-Nov-201909-Jan-2020324.17
473Hist18-Dec-2019 31134129-Nov-201909-Jan-2020324.90
474Hist19-Dec-2019 31134129-Nov-201909-Jan-2020325.63
475Hist20-Dec-2019 31134129-Nov-201909-Jan-2020326.37
476Hist21-Dec-2019 31134129-Nov-201909-Jan-2020327.10
477Hist22-Dec-2019 31134129-Nov-201909-Jan-2020327.83
478Hist23-Dec-2019 31134129-Nov-201909-Jan-2020328.56
479Hist24-Dec-2019 31134129-Nov-201909-Jan-2020329.29
480Hist25-Dec-2019 31134129-Nov-201909-Jan-2020330.02
481Hist26-Dec-2019 31134129-Nov-201909-Jan-2020330.76
482Hist27-Dec-2019 31134129-Nov-201909-Jan-2020331.49
483Hist28-Dec-2019 31134129-Nov-201909-Jan-2020332.22
484Hist29-Dec-2019 31134129-Nov-201909-Jan-2020332.95
485Hist30-Dec-2019 31134129-Nov-201909-Jan-2020333.68
486Hist31-Dec-2019 31134129-Nov-201909-Jan-2020334.41
487Hist01-Jan-2020 31134129-Nov-201909-Jan-2020335.15
488Hist02-Jan-2020 31134129-Nov-201909-Jan-2020335.88
489Hist03-Jan-2020 31134129-Nov-201909-Jan-2020336.61
490Hist04-Jan-2020 31134129-Nov-201909-Jan-2020337.34
491Hist05-Jan-2020 31134129-Nov-201909-Jan-2020338.07
492Hist06-Jan-2020 31134129-Nov-201909-Jan-2020338.80
493Hist07-Jan-2020 31134129-Nov-201909-Jan-2020339.54
494Hist08-Jan-2020 31134129-Nov-201909-Jan-2020340.27
495Hist09-Jan-202034134134109-Jan-202009-Jan-2020341.00
496Hist10-Jan-2020 34135009-Jan-202010-Feb-2020341.28
497Hist11-Jan-2020 34135009-Jan-202010-Feb-2020341.56
498Hist12-Jan-2020 34135009-Jan-202010-Feb-2020341.84
499Hist13-Jan-2020 34135009-Jan-202010-Feb-2020342.13
500Hist14-Jan-2020 34135009-Jan-202010-Feb-2020342.41
501Hist15-Jan-2020 34135009-Jan-202010-Feb-2020342.69
502Hist16-Jan-2020 34135009-Jan-202010-Feb-2020342.97
503Hist17-Jan-2020 34135009-Jan-202010-Feb-2020343.25
504Hist18-Jan-2020 34135009-Jan-202010-Feb-2020343.53
505Hist19-Jan-2020 34135009-Jan-202010-Feb-2020343.81
506Hist20-Jan-2020 34135009-Jan-202010-Feb-2020344.09
507Hist21-Jan-2020 34135009-Jan-202010-Feb-2020344.38
508Hist22-Jan-2020 34135009-Jan-202010-Feb-2020344.66
509Hist23-Jan-2020 34135009-Jan-202010-Feb-2020344.94
510Hist24-Jan-2020 34135009-Jan-202010-Feb-2020345.22
511Hist25-Jan-2020 34135009-Jan-202010-Feb-2020345.50
512Hist26-Jan-2020 34135009-Jan-202010-Feb-2020345.78
513Hist27-Jan-2020 34135009-Jan-202010-Feb-2020346.06
514Hist28-Jan-2020 34135009-Jan-202010-Feb-2020346.34
515Hist29-Jan-2020 34135009-Jan-202010-Feb-2020346.63
516Hist30-Jan-2020 34135009-Jan-202010-Feb-2020346.91
517Hist31-Jan-2020 34135009-Jan-202010-Feb-2020347.19
518Hist01-Feb-2020 34135009-Jan-202010-Feb-2020347.47
519Hist02-Feb-2020 34135009-Jan-202010-Feb-2020347.75
520Hist03-Feb-2020 34135009-Jan-202010-Feb-2020348.03
521Hist04-Feb-2020 34135009-Jan-202010-Feb-2020348.31
522Hist05-Feb-2020 34135009-Jan-202010-Feb-2020348.59
523Hist06-Feb-2020 34135009-Jan-202010-Feb-2020348.88
524Hist07-Feb-2020 34135009-Jan-202010-Feb-2020349.16
525Hist08-Feb-2020 34135009-Jan-202010-Feb-2020349.44
526Hist09-Feb-2020 34135009-Jan-202010-Feb-2020349.72
527Hist10-Feb-202035035035010-Feb-202010-Feb-2020350.00
528Hist11-Feb-2020 35036010-Feb-202010-Mar-2020350.34
529Hist12-Feb-2020 35036010-Feb-202010-Mar-2020350.69
530Hist13-Feb-2020 35036010-Feb-202010-Mar-2020351.03
531Hist14-Feb-2020 35036010-Feb-202010-Mar-2020351.38
532Hist15-Feb-2020 35036010-Feb-202010-Mar-2020351.72
533Hist16-Feb-2020 35036010-Feb-202010-Mar-2020352.07
534Hist17-Feb-2020 35036010-Feb-202010-Mar-2020352.41
535Hist18-Feb-2020 35036010-Feb-202010-Mar-2020352.76
536Hist19-Feb-2020 35036010-Feb-202010-Mar-2020353.10
537Hist20-Feb-2020 35036010-Feb-202010-Mar-2020353.45
538Hist21-Feb-2020 35036010-Feb-202010-Mar-2020353.79
539Hist22-Feb-2020 35036010-Feb-202010-Mar-2020354.14
540Hist23-Feb-2020 35036010-Feb-202010-Mar-2020354.48
541Hist24-Feb-2020 35036010-Feb-202010-Mar-2020354.83
542Hist25-Feb-2020 35036010-Feb-202010-Mar-2020355.17
543Hist26-Feb-2020 35036010-Feb-202010-Mar-2020355.52
544Hist27-Feb-2020 35036010-Feb-202010-Mar-2020355.86
545Hist28-Feb-2020 35036010-Feb-202010-Mar-2020356.21
546Hist29-Feb-2020 35036010-Feb-202010-Mar-2020356.55
547Hist01-Mar-2020 35036010-Feb-202010-Mar-2020356.90
548Hist02-Mar-2020 35036010-Feb-202010-Mar-2020357.24
549Hist03-Mar-2020 35036010-Feb-202010-Mar-2020357.59
550Hist04-Mar-2020 35036010-Feb-202010-Mar-2020357.93
551Hist05-Mar-2020 35036010-Feb-202010-Mar-2020358.28
552Hist06-Mar-2020 35036010-Feb-202010-Mar-2020358.62
553Hist07-Mar-2020 35036010-Feb-202010-Mar-2020358.97
554Hist08-Mar-2020 35036010-Feb-202010-Mar-2020359.31
555Hist09-Mar-2020 35036010-Feb-202010-Mar-2020359.66
556Hist10-Mar-202036036036010-Mar-202010-Mar-2020360.00
557F_Cast11-Mar-2020 360.34
558F_Cast12-Mar-2020 360.69
559F_Cast13-Mar-2020 361.03
560F_Cast14-Mar-2020 361.38
561F_Cast15-Mar-2020 361.72
562F_Cast16-Mar-2020 362.07
563F_Cast17-Mar-2020 362.41
564F_Cast18-Mar-2020 362.76
565F_Cast19-Mar-2020 363.10
566F_Cast20-Mar-2020 363.45
567F_Cast21-Mar-2020 363.79
568F_Cast22-Mar-2020 364.14
569F_Cast23-Mar-2020 364.48
570F_Cast24-Mar-2020 364.83
571F_Cast25-Mar-2020 365.17
572F_Cast26-Mar-2020 365.52
573F_Cast27-Mar-2020 365.86
574F_Cast28-Mar-2020 366.21
575F_Cast29-Mar-2020 366.55
576F_Cast30-Mar-2020 366.90
577F_Cast31-Mar-2020 367.24
578F_Cast01-Apr-2020 367.59
579F_Cast02-Apr-2020 367.93
580F_Cast03-Apr-2020 368.28
581F_Cast04-Apr-2020 368.62
582F_Cast05-Apr-2020 368.97
583F_Cast06-Apr-2020 369.31
584F_Cast07-Apr-2020 369.66
585F_Cast08-Apr-2020 370.00
 

Attachments

  • Forecast.ETS.JPG
    Forecast.ETS.JPG
    123 KB · Views: 5

Excel Facts

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December
Cell Formulas
RangeFormula
A93:A118A93=IF(MAX(A$3:A92)<EndofDays,(A92+1),0)
B93:B118B93=IF(C93="","",IF(C93<=maxDate,Hist,F_Cast))
C93:C118C93=IF(A93>0,C92+1,"")
D93:D118D93=IFERROR(VLOOKUP(my_EID&$C93,'[APS Daily Gain Report v1.6.xlsm]Pivots'!$AH:$AM,6,0),0)
E93:E118E93=IFERROR(IF($B93=Hist,IF($D93=0,E92,$D93),""),0)
F93:F118F93=IFERROR(IF($B93=Hist,IF($D93=0,F94,$D93),""),0)
G93:G118G93=IFERROR(IF($B93=Hist,IF($D93=0,G92,$C93),""),0)
H93:H118H93=IFERROR(IF($B93=Hist,IF($D93=0,H94,$C93),""),0)
I93:I118I93=IFERROR(IF(B93="Hist",IF(D93=0,FORECAST.ETS($C93,E93:F93,G93:H93,1),D93),FORECAST.ETS(C93,Animal_Weight_Range,Animal_Date_Range,365,1)),NA())
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,287
Members
448,562
Latest member
Flashbond

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top