How to Find Intersection of Two Trend Lines in Excel (3 Methods)

We will use a sample dataset that contains four columns along with the scatter plot. These columns represent some collected data of a company about the price and quantity of sales for the months of January and February.

Method 1 – Use Trendline Equations to Get an Intersection Point in Excel

Steps:

• Click on the scatter points of the 1st curve.
• From the Chart Elements (plus icon), go to Trendline and choose Linear.

• Double-click on the line.

A new window named Format Trendline will appear on the panel to the right.

• From Trendline Options, check Display Equation on chart.

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

• You will get the equation of the 1st Trendline. The equation format is Y=mX+c, where m is the slope, and c is the intercept.

• Find the equation for the 2nd Trendline in the same way.

• Manually insert the values of slope and intercept for both lines from the equations. You have to insert the values along with the sign.

• In the C16 cell, use the following formula:

• Press Enter and you will get the abscissa (X) of that point.

• Apply the formula given below in the C17 cell.

• Press Enter and you will get the ordinate (Y) of that point.

Read More: How to Add Trendline Equation in Excel

Method 2 – Apply the Goal Seek Feature to Calculate an Intersection Point of Two Trendlines

We have the following equations. We want to know their intersection point.

Steps:

• Equate these equations in the B6 cell using the Apostrophe (‘). We have merged the B6:D6 cells. The Apostrophe (‘) denotes that 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 put the left side in the C9 cell without the apostrophe.

• Write “0” in the C10 cell as the equation must be equal to zero.
• From the Data tab, go to Forecast.
• From What-If Analysis, choose the Goal Seek feature.

A new dialog box named Goal Seek will appear.

• Use the C9 cell reference in the Set cell box.
• Write 0 in the To value box.
• Select the C13 cell in the By changing cell box.
• Press OK.

Another dialog box named Goal Seek Status will pop up.

• Press OK.

You will get the abscissa (X) of that intercept point in the C13 cell.

• Apply the following formula in the C17 cell.

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

• Press Enter to get the ordinate (Y) of that point.

Method 3 – Combine INTERCEPT and SLOPE Functions to Find an Intersection Point of Two Trendlines

We have the following dataset of points.

Steps:

• Select a new cell C12 where you want to keep the slope of the 1st trend line.
• Use the formula given below in the cell.

• Press Enter.

• Use the corresponding formula in the C13 cell to find the intercept of the first trendline.

• Press Enter.

• Use the formula given below in the E12 cell to find the slope of the second trendline.

• Press Enter.

• Use the corresponding formula in the E13 cell to find the intercept of the second trendline.

• In the C16 cell, use the following formula.

• Press Enter and you will get the abscissa (X) of that point.

• Apply the formula given below in the C17 cell.

• Press Enter, and you will get the ordinate (Y) of that point.

Practice Section

We’ve included a simple dataset you can use for practice.

Download the Practice Workbook

Related Articles

• How to Find the Equation of a Line in Excel
• How to Find the Equation of a Trendline in Excel
• How to Show Equation in Excel Graph
• How to Create Equation from Data Points in Excel
• How to Use Trendline Equation in Excel
• How to Find Slope of Trendline in Excel

<< Go Back To Trendline in Excel | Excel Charts | Learn Excel