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.
Forecasting based on one data point is impossible. The only reasonable guess would be to use the known Y as predicted value...
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.
If you have only one "known" data point, the only possible prediction is the known Y. Any more sophisticated prediction will have to take other factors/data points into account.
Let's say I don't have access to weather forecasts.
If I have written down the maximum temperature over the last few days, I might extrapolate those data to predict tomorrow's maximum temperature.
But if I only know today's temperature and nothing else, the only prediction I can make for tomorrow is the same as today's temperature...