Forum Discussion
FORECAST function with only one known X returning DIV/0
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.
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 analysis
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.