FORECAST.ETS

[Mohamed Alidina]

Hi

i am using the FORECAST.ETS (target_date,value,timeline) but i get all weird number for my forecast.

this forecast it's for a call center and i entered the historical data for June 27th 2016 and i would like to know the forecast for June 28th. But i keep getting results in the minus, CAn somebody help me please?

 

[Eloise T]

Please send an Excel file so we can take a look at your information.

 

[Mohamed Alidina]

yes here is the file, thank you

 

[Eloise T]

It looks like: FORECAST.ETS.CONFINT is available only in Excel 2016. I'm using Excel 2007.


FORECAST.ETS.CONFINT(target_date, values, timeline [,confidence_level] [,seasonality] [,data_completion] [,aggregation])
Returns the confidence interval for a future value.

target_dateThe date you want to predict.
valuesThe historical values.
timelineThe array or range of cells.
confidence_level(Optional) A number indicating a confidence level between 0 and 1 (exclusive).
seasonality(Optional) A number indicating whether to use seasonality:
1 - automatically detected uses positive whole numbers
0 - no seasonality, therefore linear
data_completion(Optional) A number indicating how to handle missing points:
1 - use the average of the neighbouring points
0 - use zero for any missing points.
aggregation(Optional) The type of function to use:
0 = AVERAGE
1 = COUNT
2 = COUNTA
3 = MAX
4 = MIN
5 = MEDIAN
6 = SUM

 

[Hui]

Forecast.ets is a new function only available in Excel 2016+

The issue is that the trend of te data is based on 1 days data and you want to extrapolate it forward 3 days


I think it has done a good job, considering

Did you only want to use the times, instead of dates and times?

 

[Mohamed Alidina]

Hi, yes i am trying to forecast day by day and hour per hour for a call center. We don't a workforce tool so we have ti use excel. So are you saying that it can't be done on a hourly time frame?

 

[Hui]

No

I'd recomend it be done on an hourly basis, unless you have longer term data as a base, eg: 2 weeks or a month of data in which to see trends

See attached



You can change the parameters for the Forecast function using the green cells

 

[Hui]

Have you considered using a Polynomial Trendline
like:


see attached file:

 

[Mohamed Alidina]

Have you considered using a Polynomial Trendline
like:
View attachment 32259

see attached file:

Hi Hui, What is a Polynomial Trendline?
how is this formula work?

Regarding the Forecast.ets formula, few questions:
I should not have used dates with hour to do as my time line?
the results on D5 to D28, seems to be better than mine but i feel, they are still low. (I.E. cell C16 the original data is 12 but the forecast on D16 says 9. In order for me to have proper results, can i add more values like more columns?
also, the how does the aggregation work? We chose the average in this case, in averages what?
thanks