How to Use Trendline Equation in Excel (8 Suitable Examples)

An Excel trendline represents the primary pattern or direction of the data on an Excel chart. The trendline can be a straight or curved line depending on the trendline type. A trendline equation is an equation for the trendline on an Excel curve. The main focus of this article is to explain how to use trendline equation in Excel.


Download Practice workbook


8 Suitable Examples to Use Trendline Equation in Excel

To explain this article I have taken the following dataset that contains Years and Sales.

I have also taken a column chart of the dataset to show the use of the trendline equation in Excel.

8 Suitable Examples to Use Trendline Equation in Excel


1. Adding Trendline Equation in Excel

In this first example, I will explain how to add a trendline equation in Excel.

Let’s see the steps.

Steps:

  • Firstly, select the chart in which you want to add the trendline.
  • Secondly, go to Chart Elements.
  • Thirdly, select Trendline.

Now, you will see a trendline has been added to your chart.

Adding Trendline Equation in Excel

After that, you can change the color of the trendline to make it more visible.

  • Firstly, Right-click on the trendline.
  • Secondly, select Format Trendline.

Here, you will see that the Format Timeline option will appear on the right side of the screen.

  • Firstly, go to Fill & Line.
  • Secondly, select Color.

Adding Trendline Equation in Excel

Now, you will see a list of colors will appear.

  • Next, select the color you want. Here, I selected Red.

After that, you will see that you have got your desired trendline.

Adding Trendline Equation in Excel

Here, you can also change the trendline type.

  • Firstly, Right-click on the trendline.
  • Secondly, select Format Trendline.

Here, you will see that the Format Timeline option will appear on the right side of the screen.

  • Firstly, go to the Trendline Options.
  • Secondly, select the trendline type you want. Here, I kept it Linear which was set by default.  But you can select any other option you want.

Adding Trendline Equation in Excel

Now, I will add the trendline equation.

  • Firstly, Right-click on the trendline.
  • Secondly, select Format Trendline.

Here, you will see that the Format Timeline option will appear on the right side of the screen.

  • Firstly, go to the Trendline Options.
  • Secondly, select Display Equation on chart.

Adding Trendline Equation in Excel

Finally, you will see the trendline equation on the chart.

Adding Trendline Equation in Excel

Read More: How to Create Trend Chart in Excel (4 Easy Methods)


 2. Using Trendline Equation in Excel to Forecast Data

To explain this example, I have taken a dataset that contains the Temperature (K) and Volume (Liter) of a gas. I also have a column chart of the dataset with a Linear Trendline and trendline equation. Here, I will show you how to use trendline equation in Excel to forecast data.

Using Trendline Equation in Excel to Forecast Data

Let’s see the steps.

Steps:

  • Firstly, select the cell where you want to forecast the data. Here, I selected cell C10.
  • Secondly, in cell C10 write the following formula.
=0.114*B10-2.8

Here, In this formula, I used the trendline equation from the chart. I took B10 as X. Now, the formula will return the Y value.

  • After that, press ENTER to get the Y

Using Trendline Equation in Excel to Forecast Data

Now, you will see that you have got your forecasted data using the trendline equation in Excel.

Using Trendline Equation in Excel to Forecast Data

Read More: How to Insert Trendline in an Excel Cell (Create and Customization)


3. Use of Trendline Equation in Excel to Get Values for Any Given X

For this example, I have taken a dataset that contains the Temperature (K) and Volume (Liter) of a gas. I also have a column chart of the dataset with a Linear Trendline and trendline equation. Here, I will explain how to use trendline equation in Excel to get values for any given X.

Use of Trendline Equation in Excel to Get Values for Any Given X

Let’s see the steps.

Steps:

  • Firstly, select the cell where you want to get the Y value for a given X. Here, I selected cell C12.
  • Secondly, in cell C12 write the following formula.
=0.114*B12-0.8

Here, In this formula, I used the trendline equation from the chart. I took B12 as X. Now, the formula will return the Y value.

  • After that, press ENTER to get the Y

  • Now, drag the Fill Handle to copy the formula.

Use of Trendline Equation in Excel to Get Values for Any Given X

Here, you will see that you have got your Y values for any given X using the trendline equation in Excel.

Use of Trendline Equation in Excel to Get Values for Any Given X

Read More: How to Add Trendline Equation in Excel (With Easy Steps)


4. Employing Linear Trendline Equation in Excel to Find Slope and Intercept

To explain this example, I have taken a dataset that contains Years and Sales. I also have a column chart of the dataset with a linear trendline and trendline equation. Here, I will use the linear trendline equation to find the Slope and Intercept. I will also use Functions to find the Slope and Intercept for the dataset to see if it matches the trendline equation.

In the following Image, you can see that from the linear trendline equation I have got the Slope and Intercept. Now, I will use  Functions to find the Slope and Intercept for the dataset.

Let’s see the steps.

Steps:

  • Firstly, select the cell where you want to calculate the Slope. Here, I selected cell G5.
  • Secondly, in cell G5 write the following formula.
=SLOPE(C5:C9,B5:B9)

Employing Linear Trendline Equation in Excel to Find Slope and Intercept

Here, in the SLOPE  function, I selected C5:C9 as known_ys and B5:B9 as known_xs. This function will return the value of Slope for the dataset.

  • Finally, press ENTER to get the value of the Slope.

Now, I will calculate the Intercept.

  • Firstly, select the cell where you want to calculate the Intercept. Here, I selected cell G6.
  • Secondly, in cell G6 write the following formula.
=INTERCEPT(C5:C9,B5:B9)

Employing Linear Trendline Equation in Excel to Find Slope and Intercept

Here, in the INTERCEPT function, I selected C5:C9 as known_ys and B5:B9 as known_xs. This function will return the value of Intercept for the dataset.

  • Finally, press ENTER to get the value of the Intercept.

Employing Linear Trendline Equation in Excel to Find Slope and Intercept

Now, you will see that the values of the Slope and Intercept from the equation match the values that we got using functions. This means the values from the trendline equation are correct.

Read More: How to Find Slope of Trendline in Excel (2 Easy Methods)


Similar Readings


5. Use of Exponential Trendline Equation in Excel to Find the Coefficients

To explain this example, I have taken a dataset that contains Years and Sales. I also have a column chart of the dataset with an exponential trendline and trendline equation. Here, I will use the exponential trendline equation to find Coefficient a and Coefficient b. I will also use Functions to find Coefficient a and Coefficient b for the dataset to see if they match the trendline equation.

In the following Image, you can see that from the exponential trendline equation I have got Coefficient a and Coefficient b. Now, I will use Functions to find Coefficient a and Coefficient b for the dataset.

Let’s see the steps.

Steps:

  • Firstly, select the cell where you want to calculate the Coefficient a. Here, I selected cell G5.
  • Secondly, in cell G5 write the following formula.
=EXP(INDEX(LINEST(LN(C5:C9),B5:B9),1,2))

Use of Exponential Trendline Equation in Excel to Find the Coefficients

Formula Breakdown

  • Here, in the EXP function, I used an INDEX function as number which will return the value from a range.
  • Now, in the INDEX function, I used a LINEST function as array and 1 as row_num, and 2 as column_num.
  • Next, In the LINEST function, I selected LN(C5:C9) as known_ys the LN function will return the natural logarithm of the given range, and B5:B9 as known_xs.
  • Finally, the EXP function will return the result of the constant e raised to the power of the number which will be our coefficient a.
  • Thirdly, press ENTER to get the coefficient a.

Now, I will calculate Coefficient b.

  • Firstly, select the cell where you want to calculate the Coefficient b. Here, I selected cell G6.
  • Secondly, in cell G6 write the following formula.
=INDEX(LINEST(LN(C5:C9),B5:B9),1)

Use of Exponential Trendline Equation in Excel to Find the Coefficients

Formula Breakdown

  • Here, in the INDEX function, I used a LINEST function as array and 1 as row_num.
  • Next, In the LINEST function, I selected LN(C5:C9) as known_ys which will return the natural logarithm of the given range, and B5:B9 as known_xs.
  • Finally, the INDEX function will return the value from the table which will be Coefficient b.
  • Thirdly, press ENTER to get the Coefficient b.

Now, you will see that the values of Coefficient a and Coefficient b from the equation match the values that we got using functions. This means the values from the trendline equation are correct.

Use of Exponential Trendline Equation in Excel to Find the Coefficients

Read More: How to Add Trendline in Excel Online (with Easy Steps)


6. Applying Logarithmic Trendline Equation in Excel

To explain this example, I have taken a dataset that contains Years and Sales. I also have a column chart of the dataset with a logarithmic trendline and trendline equation. Here, I will use the logarithmic trendline equation to find Constant a and Constant b. I will also use Functions to find Constant a and Constant b for the dataset to see if they match the trendline equation.

In the following Image, you can see that from the logarithmic trendline equation I have got Constant a and Constant b. Now, I will use Functions to find Constant a and Constant b for the dataset.

Applying Logarithmic Trendline Equation in Excel

Let’s see the steps.

Steps:

  • Firstly, select the cell where you want to calculate the Constant a. Here, I selected cell G5.
  • Secondly, in cell G5 write the following formula.
=INDEX(LINEST(C5:C9,LN(B5:B9)),1)

Formula Breakdown

  • Here, in the INDEX function, I used a LINEST function as array and 1 as row_num.
  • Next, In the LINEST function, I selected C5:C9 as known_ys, and LN(B5:B9) as known_xs which will return the natural logarithm of the given range.
  • Finally, the INDEX function will return the value from the table which will be Constant a.
  • Thirdly, press ENTER to get the Constant a.

Now, I will calculate Constant b.

  • Firstly, select the cell where you want to calculate the Constant b. Here, I selected cell G6.
  • Secondly, in cell G6 write the following formula.
=INDEX(LINEST(C5:C9,LN(B5:B9)),1,2)

Applying Logarithmic Trendline Equation in Excel

Formula Breakdown

  • Here, in the INDEX function, I used a LINEST function as array and 1 as row_num and 2 as column_num.
  • Next, In the LINEST function, I selected C5:C9 as known_ys, and LN(B5:B9) as known_xs which will return the natural logarithm of the given range.
  • Finally, the INDEX function will return the value from the table which will be Constant b.
  • Thirdly, press ENTER to get the Constant b.

Now, you will see that the values of Constant a and Constant b from the equation match the values that we got using functions. This means the values from the trendline equation are correct.

Applying Logarithmic Trendline Equation in Excel

Read More: How to Calculate Trend Analysis in Excel (3 Easy Methods)


7. Use of Polynomial Trendline Equation in Excel

To explain this example, I have taken a dataset that contains Years and Sales. I also have a column chart of the dataset with a Polynomial trendline and trendline equation. Here, I will use the Polynomial trendline equation to find Coefficient b1, Coefficient b2, and Constant a. I will also use Functions to find Coefficient b1, Coefficient b2, and Constant a for the dataset to see if they match the trendline equation.

In the following Image, you can see that from the Polynomial trendline equation I have got Coefficient b1, Coefficient b2, and Constant a. Now, I will use Functions to find Coefficient b1, Coefficient b2, and Constant a for the dataset.

Let’s see the steps.

Steps:

  • Firstly, select the cell where you want to calculate the Coefficient b1. Here, I selected cell G5.
  • Secondly, in cell G5 write the following formula.
=INDEX(LINEST(C5:C9,B5:B9^{1,2}),1)

Use of Polynomial Trendline Equation in Excel

Formula Breakdown

  • Here, in the INDEX function, I used a LINEST function as array and 1 as row_num.
  • Next, In the LINEST function, I selected C5:C9 as known_ys, and B5:B9^{1,2} as known_xs which will raise the range B5:B9 to the power of {1,2}.
  • Finally, the INDEX function will return the value from the table which will be Coefficient b1.
  • Thirdly, press ENTER to get Coefficient b1.

Now, I will calculate Coefficient b2.

  • Firstly, select the cell where you want to calculate the Coefficient b2. Here, I selected cell G6.
  • Secondly, in cell G6 write the following formula.
=INDEX(LINEST(C5:C9,B5:B9^{1,2}),1,2)

Use of Polynomial Trendline Equation in Excel

Formula Breakdown

  • Here, in the INDEX function, I used a LINEST function as array, 1 as row_num and, 2 as column_num.
  • Next, In the LINEST function, I selected C5:C9 as known_ys, and B5:B9^{1,2} as known_xs which will raise the range B5:B9 to the power of {1,2}.
  • Finally, the INDEX function will return the value from the table which will be Coefficient b2.
  • Thirdly, press ENTER to get Coefficient b2.

Here, I will calculate Constant a.

  • Firstly, select the cell where you want to calculate the Constant a. Here, I selected cell G7.
  • Secondly, in cell G7 write the following formula.
=INDEX(LINEST(C5:C9,B5:B9^{1,2}),1,3)

Use of Polynomial Trendline Equation in Excel

Formula Breakdown

  • Here, in the INDEX function, I used a LINEST function as array, 1 as row_num and, 3 as column_num.
  • Next, In the LINEST function, I selected C5:C9 as known_ys, and B5:B9^{1,2} as known_xs which will raise the range B5:B9 to the power of {1,2}.
  • Finally, the INDEX function will return the value from the table which will be Constant a.
  • Thirdly, press ENTER to get Constant a.

Now, you will see that the values of Coefficient b1, Coefficient b2, and Constant a from the equation match the values that we got using functions. This means the values from the trendline equation are correct.

Use of Polynomial Trendline Equation in Excel

Read More: How to Find Slope of Polynomial Trendline in Excel (with Detailed Steps)


8. Using Power Trendline Equation in Excel

To explain this example, I have taken a dataset that contains Years and Sales. I also have a column chart of the dataset with a power trendline and trendline equation. Here, I will use the power trendline equation to find Constant a and Constant b. I will also use Functions to find Constant a and Constant b for the dataset to see if they match the trendline equation.

In the following Image, you can see that from the power trendline equation I have got Constant a and Constant b. Now, I will use Functions to find Constant a and Constant b for the dataset.

Let’s see the steps.

Steps:

  • Firstly, select the cell where you want to calculate the Constant a. Here, I selected cell G5.
  • Secondly, in cell G5 write the following formula.
=EXP(INDEX(LINEST(LN(C5:C9),LN(B5:B9), ,),1,2))

Using Power Trendline Equation in Excel

Formula Breakdown

  • Here, in the EXP function, I used an INDEX function as a number which will return the value from a range.
  • Now, in the INDEX function, I used a LINEST function as an array and 1 as row_num, and 2 as column_num.
  • Next, In the LINEST function, I selected LN(C5:C9) as known_ys which will return the natural logarithm of the given range, and LN(B5:B9) as known_xs which will return the natural logarithm of the given range, and left const and stats
  • Finally, the EXP function will return the result of the constant e raised to the power of the number which will be our Constant a.
  • Thirdly, press ENTER to get the Constant a.

Now, I will calculate Constant b.

  • Firstly, select the cell where you want to calculate the Constant a. Here, I selected cell G6.
  • Secondly, in cell G6 write the following formula.
=INDEX(LINEST(LN(C5:C9),LN(B5:B9),,),1)

Using Power Trendline Equation in Excel

Formula Breakdown

  • Here, in the INDEX function, I used a LINEST function as an array and 1 as row_num.
  • Next, In the LINEST function, I selected LN(C5:C9) as known_ys which will return the natural logarithm of the given range, and LN(B5:B9) as known_xs which will also return the natural logarithm of the given range, and left const and stats
  • Finally, the INDEX function will return the value from the table which will be Constant b.
  • Thirdly, press ENTER to get the Constant b.

Now, you will see that the values of Constant a and Constant b from the equation match the values that we got using functions. This means the values from the trendline equation are correct.

Read More: [Solved]: Trendline Option Not Showing in Excel (3 Solutions)


Practice Section

Here, I have provided a practice sheet for you to practice how to use trendline equation in Excel.


Conclusion

To conclude, in this article I tried to show different examples of how to use trendline equation in Excel. I hope this article was helpful. If you like this article then you can visit ExcelDemy for more articles like this. Lastly, if you have any questions feel free to let me know in the comment section below.


Related Articles

Mashhura

Mashhura

Hey! Welcome to my profile. Right now, I am doing research on Microsoft Excel. I will be posting articles related to this here. My last educational degree was B.Sc in Industrial and Production Engineering from Bangladesh University of Engineering and Technology. I like to explore new things and find the best and most innovative solutions in every situation.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo