Excel: How to Find Trendline Equation Without Chart


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

One Reply to “Excel: How to Find Trendline Equation Without Chart”

  1. Hi Zach,

    Do you know if you can do this for a quadratic? Right now, I’m having to continuously update a graph with new y-values and there’s about 200 more data sets I need to do this for.

    Thanks,

    Thomas

Leave a Reply

Your email address will not be published. Required fields are marked *