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:
`=(E13-C13)/(C12-E12)`
• Press Enter and you will get the abscissa (X) of that point.

• Apply the formula given below in the C17 cell.
`=C16*C12+C13`
• Press Enter and you will get the ordinate (Y) of that point.

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.
`=-4.239*C13+923.36`

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.
`=SLOPE(C5:C9,B5:B9)`
• Press Enter.

• Use the corresponding formula in the C13 cell to find the intercept of the first trendline.
`=INTERCEPT(C5:C9,B5:B9)`
• Press Enter.

• Use the formula given below in the E12 cell to find the slope of the second trendline.
`=SLOPE(E5:E9,D5:D9)`
• Press Enter.

• Use the corresponding formula in the E13 cell to find the intercept of the second trendline.
`=INTERCEPT(E5:E9,D5:D9)`

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

• Apply the formula given below in the C17 cell.
`=C16*C12+C13`
• 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.

Related Articles

<< Go Back To Trendline in Excel | Excel Charts | 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