When analyzing data, you might want to know not just the slope and intercept of a trend line but also deeper regression statistics like standard errors, R² values, and more.
Excel’s LINEST function provides these details, making it one of the most powerful statistical tools available in spreadsheets. While SLOPE, INTERCEPT, and TREND give specific results, LINEST gives you the full regression analysis.
🔍 What is the LINEST Function?
The LINEST function returns statistics that describe a linear trend using the least-squares regression method.
Syntax:
-
known_y’s → The dependent variable (results).
-
known_x’s (optional) → The independent variable(s). If omitted, Excel assumes {1,2,3,…}.
-
const (optional) → TRUE = calculate intercept (default), FALSE = force intercept = 0.
-
stats (optional) → TRUE = return additional regression statistics, FALSE = only slope and intercept.
💡 Note: LINEST is an array function. In older Excel versions, you must enter it with Ctrl+Shift+Enter. In modern Excel (Office 365/2021+), it works with dynamic arrays automatically.
✅ Example 1: Simple Linear Regression
Data:
| X (Study Hours) | Y (Score) |
|---|---|
| 1 | 55 |
| 2 | 60 |
| 3 | 65 |
| 4 | 70 |
Formula:
Result: {5, 50}
✔️ Explanation:
-
Slope = 5
-
Intercept = 50
Equation:
✅ Example 2: Returning Regression Statistics
To get more than just slope and intercept, use stats = TRUE.
Formula (entered across multiple cells):
Result (array output):
| Slope | Intercept | R² | Standard Error Slope | Standard Error Intercept |
|---|---|---|---|---|
| 5 | 50 | 1.0 | 0 | 0 |
✔️ Explanation:
-
R² = 1 means perfect linear fit.
-
Standard errors = 0, since the data aligns exactly with the regression line.
✅ Example 3: Multiple Regression
LINEST can also handle multiple independent variables.
| X1 (Ads) | X2 (Staff) | Y (Sales) |
|---|---|---|
| 1,000 | 5 | 20,000 |
| 2,000 | 7 | 30,000 |
| 3,000 | 9 | 40,000 |
Formula:
✔️ Explanation: LINEST calculates coefficients for both X1 and X2, giving you a multiple regression model:
🎯 Practical Uses of LINEST
-
Business Forecasting → Build predictive sales or cost models.
-
Scientific Research → Perform regression analysis on experimental data.
-
Finance → Evaluate trends in stock prices or risk-return relationships.
-
Education → Teach regression concepts with hands-on Excel examples.
📝 Conclusion
The LINEST function in Excel is more than just slope and intercept—it’s a full regression analysis tool.
👉 Use LINEST when you need detailed regression statistics or when working with multiple independent variables. It’s a powerful function for analysts, researchers, and anyone who wants deeper insights from their data.
