How to Find Intercept of Two Lines in Excel (3 Suitable Ways)

Get FREE Advanced Excel Exercises with Solutions!

An intercept of two lines represents the solution for these equations and you may need to find that point using Excel. So, if you are looking for how to find that common point of two lines in Excel then you have come to the right place. Today, I will demonstrate how to find the intercept of two lines in Excel.

Furthermore, for conducting the session, I will use Microsoft 365 version.


3 Effective Methods to Find Intercept of Two Lines in Excel

Here, I will describe 3 effective methods to find out the interception of two lines in Excel. In addition, for your better understanding, I’m going to use a sample dataset. Which contains four columns. These columns represent some collected data of a company about the price, and quantity of sales for the month of January and February. The dataset is given below.

Dataset to Find Intercept of Two Lines in Excel


1. Merge SLOPE & INTERCEPT Functions to Find Common Point in Excel

You can use a combination of SLOPE and INTERCEPT functions to find the intercept of two lines in Excel. Along with this, you have to use two generic formulas to find the point of interception. Now, let’s follow the steps.

Steps:

  • Firstly, you must select a new cell C12 where you want to keep the slope of the 1st line.
  • Secondly, you should use the formula given below in the C12 cell.
=SLOPE(C5:C9,B5:B9)

Here, in this formula, the SLOPE function will return the slope of the specified equation. Where C5:C9 is the y values range and B5:B9 is the x values range.

Use SLOPE Function to Find Intercept of Two Lines in Excel

  • Subsequently, press ENTER.

  • After that, write down the corresponding formula in the C13 cell to find the intercept of the 1st line.
=INTERCEPT(C5:C9,B5:B9)

In this formula, the INTERCEPT function will give the intercept of the specified equation using regression analysis. Where C5:C9 is the y values range and B5:B9 is the x values range.

  • Then, press ENTER to get the intercept of the 1st line.

Apply INTERCEPT Function to Find Intercept of Two Lines in Excel

  • In the same way, write down the formula given below in the E12 cell to find the slope of the 2nd line.
=SLOPE(E5:E9,D5:D9)

Here, in this formula, the SLOPE function will return the slope of a certain equation. Where E5:E9 is the y values range and D5:D9 is the x values range.

  • Then, press ENTER to get the slope of the 2nd line.

  • Similarly, write down the corresponding formula in the E13 cell to find the intercept of the 2nd line.
=INTERCEPT(E5:E9,D5:D9)

Basically, in this formula, the INTERCEPT function will give the intercept of the specified equation using regression analysis. Where E5:E9 is the y values range and D5:D9 is the x values range.

  • Consequently, press ENTER to get the intercept of the 2nd line.

At this time, I will find out the coordinate of that intercept point.

  • So, in the C16 cell, use the following formula.
=(E13-C13)/(C12-E12)
  • Then, press ENTER, and you will get the abscissa (X) of that point.

  • Then, apply the formula given below in the C17 cell.
=C16*C12+C13
  • Finally, press ENTER, and you will get the ordinate (Y) of that point.

Find Coordinate of Intercept of Two Lines in Excel

Read More: How to Find x-Intercept in Excel


2. Utilize Goal Seek Feature to Find Common Point of Two Lines

Here, you can employ the Data tab to find the common point or intercept of two lines in Excel. Suppose you have the following two equations. At this moment, you want to know the intercept of them.

Equations to Find Interception of Two Lines in Excel

Now, let’s see the steps.

Steps:

  • At first, equate these equations in the B6 cell using the Apostrophe (). Here, I have merged B6:D6 cells. Moreover, the Apostrophe () denotes that this is a text. Otherwise, you can’t equate these equations directly.

  • Then, move the right-hand side to the left-hand side, using the equation law.

  • After that, use the C13 cell reference instead of X in the above equation and keep this in the C9 cell.

  • Now, write “0” in the C10 cell as the equation must be equal to zero.
  • Then, from the Data tab >> go to Forecast >> then from What-If Analysis >> choose Goal Seek.

Using Goal Seek Feature to Find Interception of Two Lines in Excel

As a result, a new dialog box named Goal Seek will appear.

  • Firstly, use the C9 cell reference in the Set cell box.
  • Secondly, write 0 in the To value box.
  • Thirdly, select the C13 cell in the By changing cell box.
  • Lastly, press OK.

Subsequently, another dialog box named Goal Seek Status will pop up.

  • Here, press OK.

As a result, you will see the abscissa (X) of that intercept point.

  • Lastly, apply the following formula in the C17 cell.
=-4.239*C13+923.36

Basically, I have used the C13 cell value (X) in the 1st equation to find the Y value.

  • After pressing ENTER, you will get the ordinate (Y) of that point.


3. Use of Excel Scatter Chart for Finding Intercept of Two Lines

You can use the Scatter chart to get the slope and intercept of two lines in Excel. Furthermore, using these, you can find the coordinate of the common point. Now, follow the steps given below.


Step-1: Find Linear Equations for Two Lines in Excel

  • Firstly, you have to select the data of the 1st line. Here, I have selected the range B4:C9.
  • Secondly, you have to go to the Insert tab.
  • Thirdly, from the Charts group section you have to go to Insert Scatter (X, Y) or Bubble Chart >> then choose Scatter.

Use of Scatter Chart to Find Intercept of Two Lines in Excel

  • As a result, you will see the following scatter points of 1st line.

  • At this time, you need to select the Chart.
  • Then, from the Chart Design tab >> you have to go to Select Data under Data command.

Here, without selecting the Chart, the Chart Design tab will not be in your top ribbon. Furthermore, you can do it by Right-Clicking on the Chart and using the Context Menu Bar.

After that, you will see the following dialog box named Select Data Source.

  • Now, from the dialog box of Select Data Source, you have to choose the Add feature.

After selecting the Add feature, another dialog box of Edit Series will appear.

  • Now, you can write down or select the Series name in that dialog box. Here, I have written the Series name as Feb.
  • Then, you have to include the Series X values. Here, I have used the range D5:D9.
  • Then, you have to include the Series Y values. Here, I have used the range E5:E9.
  • Finally, you need to press OK.

Subsequently, the previous dialog box of Select Data Source will appear.

  • Now, from this, you may select Sales Unit (Jan) >> then choose the Edit option.

At this time, a dialog box named Edit Series will appear.

  • Then, you have to write the Series name. Here, I have written the Series name as Jan.
  • Now, press OK.

  • After this, press OK on the Select Data Source box.

  • As a result, you will see points of the 2nd line.

Draw Scatter Charts to Find Intercept of Two Lines in Excel

  • Then, click on the scatter points of the 1st line.
  • After that, from the Plus icon >> go to Trendline >> choose Linear.

Consequently, you will see the added trendline for the 1st line.

  • Then, double-click on that line.

So, a new window named Format Trendline will appear on the rightmost side of the Excel sheet.

  • Now, from Trendline Options >> check Display Equation on chart.

  • Furthermore, from the Fill & Line menu >> you can change the Dash type to Solid line.

  • Lastly, you will get the equation of the 1st line.

Basically, the equation format is Y=mX+c. Where m is the slope and c is the intercept.

  • Similarly, find the equation for the 2nd line.

Find Linear Equations of Two Lines in Excel


Step-2: Find Coordinate of Intercept Point of Two Lines in Excel

  • Then, manually insert the values of slope and intercept for both lines using the equations.

At this time, I will find out the coordinate of that intercept point.

  • So, in the C16 cell, use the following formula.
=(E13-C13)/(C12-E12)
  • Then, press ENTER, and you will get the abscissa (X) of that point.

  • Then, apply the formula given below in the C17 cell.
=C16*C12+C13
  • Lastly, press ENTER, and you will get the ordinate (Y) of that point.

Read More: How to Set Intercept Trendline in Excel


Practice Section

Now, you can practice the explained method by yourself.

Practice Section to Find Intercept of Two Lines in Excel


Download Practice Workbook

Download this practice sheet to practice while you are reading this article.


Conclusion

I hope you found this article helpful. Here, I have explained 3 suitable methods to find the intercept of two lines in Excel. Please drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles


<< Go Back to Excel INTERCEPT Function | Excel Functions | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Musiha Mahfuza Mukta
Musiha Mahfuza Mukta

Musiha Mahfuza Mukta is a marine engineer who loves exploring Excel and VBA programming. To her, programming is a time-saving tool for dealing with data, files, and the internet. She's skilled in Rhino3D, Maxsurf, C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. With a B.Sc in Naval Architecture & Marine Engineering from BUET, she's shifted gears and now works as a content developer. In this role, she creates techy content exclusively focused on Excel... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo