Forum Discussion
Solved
FORECAST function with only one known X returning DIV/0
Hi all
I'm trying to use the forecast formula to predict Y (case_irt_sla_bill) based on one hypothetical X (fte_bill), one known Y (case_irt_sla_actual) and one known X (fte_actual).
I have this data for all months in the year, and using forecast with all known Xs and Ys gives no error, but I want to forecast the unknown X based only on the Y and X for that given month (ie all in the same row). I believe the lack of variance due to having only one X is what is giving me the DIV/0.
Is there any workaround I could use?
Thanks in advance
Ber93 wrote: ``I thought there might be some way to establish a correlation between known X and known Y and then use that to predict unknown Y based on hypothetical X``
So which is it: Forecast 1, Forecast 2 or Forecast 3 (which is an infinite number of forecasts)?
Forecast 1: estY = x*(0.85 / 65.95) + 0
Forecast 2: estY = x + (65.95 - 0.85)
Forecast 3: estY = x*(0.85 - b)/65.95 + b, b = -RAND() (pick one)
PS.... And HansVogelaar assumes Forecast 4: estY = x*0 + 0.85
To appreciate Forecast 3, download the attached Excel file and press f9 repeatedly. It is intended to demonstrate there are an "infinite" number of forecasts of the form a*x + b. The formula for Forecast 3 is not the only one.
And that assumes that there is a linear relationship between X and estY.
How do you know that?
Having multiple actual x-y pairs will confirm the linear relationship or identify another possible relationship.
4 Replies
Forecasting based on one data point is impossible. The only reasonable guess would be to use the known Y as predicted value...
- I thought there might be some way to establish a correlation between known X and known Y and then use that to predict unknown Y based on hypothetical X.
Probably not with Forecast, but a different formula. My problem is that when using the data points from all months, the estimate Y for some months is higher than the actual Y, even if hypothetical X is lower than actual X.
I know this is because there are other variables affecting Y, but I cannot account for them in my analysisBer93 wrote: ``I thought there might be some way to establish a correlation between known X and known Y and then use that to predict unknown Y based on hypothetical X``
So which is it: Forecast 1, Forecast 2 or Forecast 3 (which is an infinite number of forecasts)?
Forecast 1: estY = x*(0.85 / 65.95) + 0
Forecast 2: estY = x + (65.95 - 0.85)
Forecast 3: estY = x*(0.85 - b)/65.95 + b, b = -RAND() (pick one)
PS.... And HansVogelaar assumes Forecast 4: estY = x*0 + 0.85
To appreciate Forecast 3, download the attached Excel file and press f9 repeatedly. It is intended to demonstrate there are an "infinite" number of forecasts of the form a*x + b. The formula for Forecast 3 is not the only one.
And that assumes that there is a linear relationship between X and estY.
How do you know that?
Having multiple actual x-y pairs will confirm the linear relationship or identify another possible relationship.
