Statistical
FORECAST function: project a linear trend from historical X and Y pairs
Estimate future demand or utility load along a least-squares linear regression line using FORECAST, and guard against flat X inputs.
Fit a straight line without manually computing slope and intercept
When a metric grows roughly in proportion to an operational driver—such as monthly warehouse electricity (kWh) rising with packed orders—FORECAST(x, known_y's, known_x's) fits an ordinary least-squares line y = a + bx and returns the predicted y at target x.
Notice the argument order: known_y's (the dependent outcome you want to predict) comes before known_x's (the explanatory driver).
| Packed Orders (A) | Electricity kWh (B) | Notes |
|---|---|---|
| 100 | 1450 | Month 1 observation |
| 200 | 1900 | Month 2 observation |
| 300 | 2350 | Month 3 observation |
| 400 | 2800 | Month 4 observation (base 1,000 kWh + 4.5 kWh/order) |
Project at a future driver volume or zero-volume baseline
Evaluating FORECAST at D2 = 500 packed orders projects 1,000 + 4.5 * 500 = 3,250 kWh. Evaluating at x = 0 returns the regression intercept (1,000 kWh fixed base load).
If Month 4 rises to 2,900 kWh, the least-squares slope adjusts across all four points to 4.8 kWh per order with a 950 kWh intercept, projecting 3,350 kWh at 500 orders.
This projection assumes the fitted linear relationship continues beyond the observed inputs. It is an estimate, not a guaranteed outcome, and this formula alone does not provide an uncertainty interval.
=FORECAST(D2, B2:B5, A2:A5)Guard against zero variance in known_x's
A regression slope divides by the variance of known_x's. If every historical x value is identical (for example, 200 orders in all four months), no unique line exists. Checking VAR.S(A2:A5)=0 lets you return a clear message before running FORECAST.
=IF(VAR.S(A2:A5)=0, "Need varying X", FORECAST(D2, B2:B5, A2:A5))Try the example
Use the original values or change them to test your understanding.