One way to find a trendline equation in Excel is to create a scatter plot and then insert a trendline into the chart:
However, a faster way to find a trendline equation without creating a chart is by using the LINEST() function in Excel, which uses the following syntax:
LINEST(known_x’s, known_y’s)
where:
- known_x’s: A column of values for the response variable
- known_y’s: A column of values for the predictor variable
This function produces a value for both the intercept and slope of the trendline.
The following example shows how to use the LINEST function in practice in Excel to find a trendline equation for two variables.
Example: How to Find Trendline Equation Without Chart in Excel
Suppose we have the following dataset in Excel:
Now suppose that we would like to find a trendline equation that summarizes the relationship between these two variables.
We can type the following formula into cell D2 to find the trendline equation:
=LINEST(B2:B14, A2:A14)
The following screenshot shows how to use this formula in practice:
The first value in the output represents the slope of the trendline and the second value represents the intercept of the trendline.
Using these two values, we can write the trendline equation for this dataset:
y = 0.552326x + 2.401163
Here is how to interpret this equation:
- A one-unit increase in x is associated with an average increase of 0.552326 in y.
- When x is equal to zero, the average value of y is 2.401163.
By using the LINEST function, we were able to find the equation of the trendline without creating any graph.
Additional Resources
The following tutorials explain how to perform other common tasks in Excel:
How to Create a Line of Best Fit in Excel
How to Interpret Output of LINEST Function in Excel
How to Use LINEST to Perform Multiple Linear Regression in Excel
