# How to Find the Intercept of Two Lines in Excel – 3 Methods

The sample dataset showcases prices and sales data in January and February.

### Method 1 – Combining the SLOPE & INTERCEPT Functions to Find a Common Point in Excel

Steps:

• Select a new cell: C12, to keep the slope of the 1st line.
• Use the formula below in C12.
`=SLOPE(C5:C9,B5:B9)`

The SLOPE function returns the slope of the equation. C5:C9 is the y range and B5:B9 is the x range.

• Press ENTER.

• Enter the formula in C13 to find the intercept of the 1st line.
`=INTERCEPT(C5:C9,B5:B9)`

The INTERCEPT function returns the intercept of the equation using a regression analysis. C5:C9 is the y range and B5:B9 is the x range.

• Press ENTER to see the intercept of the 1st line.

• Enter the formula below in E12 to find the slope of the 2nd line.
`=SLOPE(E5:E9,D5:D9)`

The SLOPE function returns the slope of the equation. E5:E9 is the y range and D5:D9 is the x range.

• Press ENTER to see the slope of the 2nd line.

• Enter the formula in E13 to find the intercept of the 2nd line.
`=INTERCEPT(E5:E9,D5:D9)`

The INTERCEPT function returns the intercept of the equation using a regression analysis. E5:E9 is the y range and D5:D9 is the x range.

• Press ENTER to see the intercept of the 2nd line.

Find the coordinate of the intercept point.

• In C16, use the following formula.
`=(E13-C13)/(C12-E12)`
• Press ENTER, and you will get the abscissa (X) of that point.

• Use the formula below in C17.
`=C16*C12+C13`
• Press ENTER, and you will get the ordinate (Y) of that point.

Read More: How to Find x-Intercept in Excel

### Method 2 – Utilize the Goal Seek Feature to Find a Common Point of Two Lines

You have the following two equations and want to know their intercept point.

Steps:

• Enter the equations in B6 using the Apostrophe (). Here, B6:D6 were merged. The Apostrophe () denotes this is a text.

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

• Use the C13 cell reference instead of X in the above equation and keep it in C9.

• Enter “0” in C10 (the equation must be equal to zero).
• In the Data tab >> go to Forecast >> What-If Analysis >>Goal Seek.

In the Goal Seek dialog box:

• Use the C9 cell reference in Set cell.
• Enter 0 in To value.
• Select C13 in By changing cell.
• Click OK.

In Goal Seek Status:

• Click OK.

You will see the abscissa (X) of that intercept point.

• Use the following formula in C17.
`=-4.239*C13+923.36`

C13 cell value (X) is used in the 1st equation to find the Y value.

• Press ENTER to see the ordinate (Y) of that point.

### Method 3 – Use the Excel Scatter Chart to Find the Intercept of Two Lines

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

• Select the data of the 1st line. Here, B4:C9.
• Go to the Insert tab.
• In Charts, go to Insert Scatter (X, Y) or Bubble Chart >> choose Scatter.

• You will see the following scatter points of the 1st line.

• Select the Chart.
• In Chart Design >> go to Select Data in Data.

The Chart Design tab will only displayed o the ribbon if the chart is selected. You can display it by Right-Clicking the Chart and using the Context Menu Bar.

You will see the Select Data Source dialog box.

In Edit Series:

• Enter or select the Series name. Here, Feb.
• Enter the Series X values. Here, D5:D9.
• Enter the Series Y values. Here, E5:E9.
• Click OK.

In Select Data Source:

• Select Sales Unit (Jan) >> choose Edit.

In Edit Series:.

• Enter the Series name. Here,  Jan.
• Click OK.

• Click OK in the Select Data Source box.

• You will see the points of the 2nd line.

• Click the scatter points of the 1st line.
• Click the Plus icon >> go to Trendline >> choose Linear.

A trendline is added to the 1st line.

• Double-click that line.

In Format Trendline:

• Select Trendline Options >> check Display Equation on chart.

• In Fill & Line >> change the Dash type to Solid line.

• You will see the equation of the 1st line.

The equation format is Y=mX+c.  m is the slope and c is the intercept.

• Find the equation for the 2nd line.

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

• Enter the values of slope and intercept for both lines using the equations.

To find the coordinate of that intercept point:

• In C16, use the following formula.
`=(E13-C13)/(C12-E12)`
• Press ENTER, and you will see the abscissa (X) of that point.

• Use the formula below in C17.
`=C16*C12+C13`
• Press ENTER, and you will see the ordinate (Y) of that point.

Read More: How to Set Intercept Trendline in Excel

Practice here.

## Related Articles

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

Get FREE Advanced Excel Exercises with Solutions!
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

Advanced Excel Exercises with Solutions PDF