What's the difference between LINEST(ys,xs) and LINEST(ys,xs,TRUE,TRUE)?

The first returns a 2-element array (slope and intercept only). The second returns a 5-row array with slopes, intercepts, standard errors, R² (fit quality), and F-statistic (hypothesis test). Use stats=TRUE to assess model reliability and determine whether your regression is strong enough for predictions.

Can LINEST handle multiple independent variables?

Yes. Provide multiple columns for known_xs (e.g., price AND salesperson experience as predictors). LINEST returns multiple slopes in the first row—one per x variable—allowing you to model Units = m1×Price + m2×Experience + b.

How do I extract one value from LINEST's array result?

Wrap LINEST in INDEX: =INDEX(LINEST(...), row, col). For example, =INDEX(LINEST(ys,xs,1,1), 1, 1) grabs the slope. Alternatively, use SLOPE() and INTERCEPT() functions for cleaner, more readable formulas.

Why does my LINEST return #NUM! when my data looks correct?

Common causes: (1) known_xs and known_ys have different row counts; (2) all x values are identical, preventing slope calculation; (3) x values are nearly identical (multicollinearity), causing numerical instability. Check for duplicate or constant x values, and verify both arrays span exactly the same range.

LINEST function

LINEST returns regression statistics for fitting a straight line through your data, including slope, intercept, and optional correlation metrics.

=LINEST(known_ys, [known_xs], [const], [stats])

Generate a LINEST formula

Describe what you need. The generator will reach for LINEST where LINEST is the right tool, and tell you when it is not.

Arguments

How LINEST reads its arguments

Argument	Required	Description
known_ys	Required	Array of dependent variable values (required); must be numeric; function returns #VALUE! if omitted or contains text.
known_xs	Optional	Array of independent variable values (optional); if omitted, defaults to sequence 1,2,3... matching known_ys length; mismatched size with known_ys causes #NUM!.
const	Optional	Boolean flag for regression intercept (optional, defaults TRUE); set FALSE to force line through origin (0,0).
stats	Optional	Boolean flag for extended statistics (optional, defaults FALSE); TRUE returns 5-row array including slopes, intercept, errors, R², and F-statistic.

Returns

Returns an array of regression coefficients and statistics; basic mode returns slope and intercept, stats=TRUE adds standard errors, R², and F-test values.

Availability

Excel: All · Google Sheets: Supported

Worked examples

1. Analyze how unit price affects quantity sold

Units	Unit_Price
5	120
8	95
3	150
10	85
7	120
6	95
4	150
9	85

=LINEST(E2:E9, D2:D9)

Result: {-0.0286, 13.43}

LINEST returns the slope (-0.0286) and intercept (13.43) for the regression line Units = −0.0286 × Price + 13.43. The negative slope indicates that higher-priced items sell in lower volumes—a typical retail inverse relationship. The intercept represents the theoretical baseline when price is zero.

2. Build a predictive model with diagnostic statistics

Units	Unit_Price
5	120
8	95
3	150
10	85
7	120
6	95
4	150
9	85

=LINEST(E2:E9, D2:D9, TRUE, TRUE)

Result: 5×2 array: Row 1: [-0.0286, 13.43]; Row 3: [0.62, ...]

With stats=TRUE, LINEST returns a 5-row array including slopes, intercepts, standard errors, R² (0.62 means price explains 62% of units variation), and F-statistic. The R² value tells you model reliability—values below 0.5 signal weak correlation and risky predictions. Use this output to decide whether your regression model is trustworthy before forecasting.

3. Forecast sales trend over consecutive time periods

Period	Total_Revenue
1	600
2	760
3	450
4	850
5	840
6	570
7	600
8	765

=LINEST(F2:F9, {1;2;3;4;5;6;7;8})

Result: {22.5, 580}

LINEST with months (1–8) as the independent variable returns slope 22.5 and intercept 580, meaning monthly revenue grows by ~$22.50 on average with a $580 baseline. To forecast month 9, calculate 22.5 × 9 + 580 = $782.50. This linear trend may mask seasonal variation, so verify the pattern visually and check R² before relying on predictions.

Common errors

Which LINEST error are you seeing?

Error	Why it happens	How to fix it
#VALUE!	known_ys contains text, blanks, or non-numeric values; or known_ys argument is missing entirely.	Ensure the known_ys column contains only numbers. Remove or convert text entries ('N/A', 'TBD', or 'pending' must become 0 or be deleted). Check for leading/trailing spaces around numbers that prevent parsing.
#NUM!	known_xs and known_ys arrays have different lengths; or all x values are identical (perfect multicollinearity), making the regression calculation unsolvable.	Verify known_xs and known_ys ranges contain exactly the same number of cells. If using constant x values (e.g., all prices are $100), the model cannot calculate a slope because there is no variation in the independent variable—add variety to x data or remove the constant column.
#REF!	Range reference in known_ys or known_xs points to deleted rows, a removed column, or a closed workbook.	Recheck range notation (D2:D9 is correct; D2:D10 may exceed your data boundary). If you deleted rows, update the LINEST range accordingly. Ensure all referenced sheets are open and the named ranges, if used, still exist.

Tips and when to use something else

Use SLOPE and INTERCEPT as simpler alternatives when you only need those two values instead of extracting them from LINEST's array output.
For single-value predictions, pair LINEST with FORECAST.LINEAR: it is clearer and shorter than manually computing m×x+b and less error-prone.
Always check the R² value (row 3, column 1 when stats=TRUE) before trusting predictions; R² below 0.5 signals weak correlation and unreliable forecasts.
LINEST assumes linearity. If your data follows a curve or exponential growth, use LOGEST instead, or first plot a scatter chart to verify the relationship is truly linear.

Related functions

SLOPESLOPE calculates the slope of a linear regression line between two data sets, showing the rate of change between variables.INTERCEPTINTERCEPT finds where a linear regression line crosses the y-axis, revealing the trend's mathematical starting point.FORECAST.LINEARFORECAST.LINEAR returns a predicted y-value by fitting a straight line through known x/y data points and extrapolating to a new x.INDEXINDEX returns the value or array of values from a specified position within a given range, based on row and optional column numbers.CORRELCORREL returns the Pearson correlation coefficient between two data sets, ranging from -1 to 1 to indicate relationship strength.

Frequently asked questions

What's the difference between LINEST(ys,xs) and LINEST(ys,xs,TRUE,TRUE)?: The first returns a 2-element array (slope and intercept only). The second returns a 5-row array with slopes, intercepts, standard errors, R² (fit quality), and F-statistic (hypothesis test). Use stats=TRUE to assess model reliability and determine whether your regression is strong enough for predictions.
Can LINEST handle multiple independent variables?: Yes. Provide multiple columns for known_xs (e.g., price AND salesperson experience as predictors). LINEST returns multiple slopes in the first row—one per x variable—allowing you to model Units = m1×Price + m2×Experience + b.
How do I extract one value from LINEST's array result?: Wrap LINEST in INDEX: =INDEX(LINEST(...), row, col). For example, =INDEX(LINEST(ys,xs,1,1), 1, 1) grabs the slope. Alternatively, use SLOPE() and INTERCEPT() functions for cleaner, more readable formulas.
Why does my LINEST return #NUM! when my data looks correct?: Common causes: (1) known_xs and known_ys have different row counts; (2) all x values are identical, preventing slope calculation; (3) x values are nearly identical (multicollinearity), causing numerical instability. Check for duplicate or constant x values, and verify both arrays span exactly the same range.

Need a different formula?

The full generator is not scoped to one function — describe any spreadsheet problem and it will pick.

Open the formula generator

Reviewed 2026-09-17