Why does TREND return #VALUE! when your known_y’s and known_x’s are the same length? Why does it silently ignore blank rows in column B but crash on a single text cell in column A? Why does =TREND(A2:A10,B2:B10,C2:C5) give you 5 numbers — but only the first matches your chart’s trendline?
The Problem
You’re forecasting Q3 sales for five regional offices using historical monthly data. Your raw sheet looks like this — messy, inconsistent, and full of traps that TREND won’t warn you about.
| Month | Region | Sales ($) | Avg. Temp (°C) |
|---|---|---|---|
| 2024-01-01 | Shanghai | $24,800 | 3.2 |
| 2024-02-01 | Shanghai | $27,150 | 5.7 |
| 2024-03-01 | Shanghai | $29,400 | 9.1 |
| 2024-04-01 | Shanghai | $31,200 | 14.3 |
| 2024-05-01 | Shanghai | $33,650 | 18.9 |
| 2024-06-01 | Shanghai | $35,200 | 22.4 |
| 2024-07-01 | Shanghai | $36,800 | 26.1 |
| 2024-08-01 | Shanghai | $38,900 | 28.7 |
| 2024-09-01 | Shanghai | — | 30.2 |
| 2024-10-01 | Shanghai | — | 27.8 |
| 2024-11-01 | Shanghai | — | 22.1 |
| 2024-12-01 | Shanghai | — | 15.4 |
You try =TREND(B2:B9,D2:D9,D10:D13) — expecting four forecasts for Sept–Dec. Instead, Excel returns #N/A in every cell. You check D10:D13 — all numbers. You retype the formula. Still #N/A. You paste values. Still broken. The frustration mounts — especially since the chart trendline works fine.
The Solution
TREND fails here not because of math, but because of structure. It demands consistent array dimensions and rejects blanks — even if they’re in the *new_x’s*, not the knowns. Here’s the exact sequence that fixes it:
| Step | Action | Result | Shortcut |
|---|---|---|---|
| 1 | Select cells E10:E13 (same height as new_x’s) | Range ready for array formula | Ctrl+Shift+Down Arrow |
| 2 | Enter =TREND(B2:B9,D2:D9,D10:D13) | Formula appears in E10 only | None yet |
| 3 | Press Ctrl+Shift+Enter (not Enter) | All four cells populate: $40,122 / $41,877 / $43,245 / $44,119 | Ctrl+Shift+Enter |
| 4 | Confirm no blanks exist in B2:B9 or D2:D9 — replace “—” with 0 or delete row | #N/A disappears; forecasts align with linear regression line | Alt+H+D+R (to delete row) |
What makes this elegant is how TREND internally computes coefficients using least squares — then applies them to each new_x without requiring you to extract slope/intercept manually. That’s why it’s faster than building =slope()*x + intercept() across dozens of rows.
Going Further
You can extend TREND beyond simple linear fits. Add a third argument — const — to force the y-intercept to zero (=TREND(known_y’s,known_x’s,new_x’s,FALSE)). Or feed it multiple x-columns: =TREND(B2:B9,C2:D9,C10:D13) for multivariate prediction (e.g., Sales ~ Temp + Ad Spend). Just ensure all arrays have matching row counts — B2:B9 (8 rows), C2:D9 (8×2), C10:D13 (4×2).
Here’s a counterintuitive tip: TREND ignores text and logical values *only* in the known_x’s — but treats them as zero in known_y’s. So if B5 contains "Pending" instead of a number, TREND uses 0 for that y-value, skewing the entire model. Always clean y-data first.
Need confidence intervals? TREND doesn’t provide them — but you can wrap it in LINEST: =INDEX(LINEST(B2:B9,D2:D9,TRUE,TRUE),2,1) pulls the standard error of the estimate.
When NOT to Use This
Don’t reach for TREND when:
- Your relationship isn’t roughly linear — e.g., viral growth (exponential), seasonality (cyclical), or saturation curves (logistic). Try LOGEST or FORECAST.ETS instead.
- You have fewer than three known points — TREND needs at least two points to compute slope, but results become meaningless with only two.
- New_x’s contain #N/A or errors — TREND propagates them. Filter or wrap with IFERROR first.
- You’re predicting outside the range of known_x’s (extrapolation) without domain knowledge — Shanghai sales likely won’t keep rising $1.8k/month when temp hits 40°C.
Also: TREND assumes homoscedasticity (equal variance across x-values). If residuals fan out (e.g., high-temp months show wilder sales swings), consider weighted regression or transforming variables.
Keyboard Shortcuts
| Shortcut | Action | Use Case |
|---|---|---|
| Ctrl+Shift+Enter | Commit array formula | Essential for TREND, TRANSPOSE, MMULT |
| Alt+H+D+R | Delete selected row(s) | Clean blank or text-ridden rows fast |
| Ctrl+G → Special → Blanks | Select all blank cells | Find & replace “—” or empty strings in known_y’s |
| Alt+= | AutoSum (for quick sum validation) | Verify known_y’s aren’t accidentally summed before TREND |
