Using LINEST for Linear Regression in Google Sheets
August 7th, 2026
Linear regression answers a practical question: given past X/Y pairs, what straight line best fits the data, and what Y should you expect for a new X? Google Sheets covers that with a small family of functions — LINEST, SLOPE, INTERCEPT, RSQ, TREND, and FORECAST.LINEAR. This post shows how they fit together with a worked example, so you can go from raw columns to slope, fit quality, fitted values, and a one-shot prediction.
A simple dataset
Suppose you track monthly ad spend and resulting sign-ups:
| Month | Spend (X) in A | Sign-ups (Y) in B | | --- | --- | --- | | 1 | 100 | 18 | | 2 | 150 | 24 | | 3 | 200 | 31 | | 4 | 250 | 35 | | 5 | 300 | 42 | | 6 | 350 | 48 |
Put spend in A2:A7 and sign-ups in B2:B7. The model you want is Y ≈ m·X + b — how many extra sign-ups per dollar of spend (m), and the baseline (b).
SLOPE, INTERCEPT, and RSQ — the quick path
When you only need the three headline numbers, use the dedicated functions.
SLOPE is the change in Y per unit of X:
=SLOPE(B2:B7, A2:A7)
Note the argument order: Y first, then X — same as LINEST. With the sample data this returns about 0.118 sign-ups per dollar spent.
INTERCEPT is where the line crosses Y when X is zero:
=INTERCEPT(B2:B7, A2:A7)
That returns roughly 6.5 for this dataset (a theoretical baseline if spend were zero — useful for the equation, not always meaningful as a business claim).
RSQ is R², the fraction of variance in Y explained by the linear relationship with X:
=RSQ(B2:B7, A2:A7)
Values near 1 mean the points hug the line closely; near 0 means the line explains almost nothing. Always check R² before trusting a forecast.
LINEST — slope, intercept, and full stats in one array
LINEST returns the same slope and intercept as above, and optionally a full statistics block.
Basic form (slope and intercept)
=LINEST(B2:B7, A2:A7)
In Google Sheets this spills (or you can enter it as an array) as two values: slope first, intercept second. So for a single independent variable you get {m, b}.
You can force the line through the origin by setting the third argument to FALSE (do not calculate a free intercept):
=LINEST(B2:B7, A2:A7, FALSE)
Only do that when theory says Y must be zero when X is zero (for example pure proportions with no baseline).
Verbose form (regression diagnostics)
Set the fourth argument to TRUE for extra statistics:
=LINEST(B2:B7, A2:A7, TRUE, TRUE)
The returned array is multi-row. For one X variable the useful cells include:
| | Column 1 | Column 2 |
| --- | --- | --- |
| Row 1 | Slope (m) | Intercept (b) |
| Row 2 | Standard error of slope | Standard error of intercept |
| Row 3 | R² | Standard error of Y estimate |
| Row 4 | F statistic | Degrees of freedom |
| Row 5 | Regression sum of squares | Residual sum of squares |
Row 1 matches SLOPE / INTERCEPT. Row 3, column 1 matches RSQ. The rest matter when you need uncertainty or formal model comparison — same territory as the broader stats toolkit covered in Statistical Distributions and Hypothesis Testing in Google Sheets.
Pulling a single statistic from LINEST
If you only want R² from the verbose array without keeping the whole block visible, wrap with INDEX:
=INDEX(LINEST(B2:B7, A2:A7, TRUE, TRUE), 3, 1)
That returns R² only. Same idea for slope with INDEX(..., 1, 1) or intercept with INDEX(..., 1, 2).
TREND — fitted values (and optional new X)
TREND applies the least-squares line to every X you already have — or to a new set of X values — and returns the predicted Y for each.
Fitted values on the original data
=TREND(B2:B7, A2:A7)
This spills six fitted sign-up counts, one per row of spend. Compare them side-by-side with actuals in column B to see residuals (B2 - fitted).
Predictions for new X values
Put future spend levels in D2:D4 (for example 400, 450, 500) and run:
=TREND(B2:B7, A2:A7, D2:D4)
TREND fits on the known pairs, then evaluates the line at each new X. That is the multi-point version of a forecast.
FORECAST.LINEAR — one prediction at a time
FORECAST.LINEAR is the simplest way to answer “what Y for this single X?”:
=FORECAST.LINEAR(400, B2:B7, A2:A7)
Argument order: new X, known Y, known X. With the sample data this returns the same result as TREND for a single new spend of 400.
FORECAST is the older name for the same linear calculation; prefer FORECAST.LINEAR when you want the formula to read clearly. Neither function invents a more complex model — both assume a straight line.
You can also rebuild the same prediction from slope and intercept:
=SLOPE(B2:B7, A2:A7)*400 + INTERCEPT(B2:B7, A2:A7)
That is useful when you already store m and b in cells for a dashboard.
Putting it together: a mini analysis block
A practical layout for the spend example:
=SLOPE(B2:B7, A2:A7)
=INTERCEPT(B2:B7, A2:A7)
=RSQ(B2:B7, A2:A7)
=TREND(B2:B7, A2:A7)
=FORECAST.LINEAR(400, B2:B7, A2:A7)
Or collapse slope/intercept/R² into one verbose LINEST spill and chart actual Y against the TREND column. For more on summarizing and filtering the source data first, see Understanding Statistical Functions in Google Sheets and Top 5 Advanced Functions for Data Analysis in Google Sheets.
Common mistakes
- Swapping X and Y.
SLOPE,INTERCEPT,RSQ,LINEST, andTRENDall take known Y first.FORECAST.LINEARis the odd one out: new X first, then known Y, then known X. - Non-numeric or mismatched ranges. X and Y ranges must be the same length and free of text/blank holes in the regression pairs.
- Extrapolating too far. A solid R² on $100–$350 spend does not guarantee the line holds at $5,000. Linear tools only project the straight-line assumption.
- Forcing intercept through zero.
LINEST(..., FALSE)orTREND(..., FALSE)changes the model; use it only when you mean it. - Confusing correlation with causation. A steep slope between spend and sign-ups does not prove spend causes sign-ups — only that the historical relationship is linear.
Which function should you pick?
| Goal | Function |
| --- | --- |
| Slope only | SLOPE |
| Intercept only | INTERCEPT |
| Fit quality (R²) | RSQ or verbose LINEST |
| Slope + intercept (+ stats) in one place | LINEST |
| Fitted or multi-point predicted Y | TREND |
| Single future Y | FORECAST.LINEAR |