How to Find Graph Intersection Point in Excel (5 Useful Methods)

Get FREE Advanced Excel Exercises with Solutions!

The graph is one of the most widely used tools in Microsoft Excel. It helps to represent certain numeric values in a more presentable way. Along with the graphical representation, we also get various intersection points between the lines based on equations. In this article, we will learn how to find graph intersection point in Excel with 5 useful methods.


Download Practice Workbook

Get this practice file and try the process by yourself.


5 Useful Methods to Find Graph Intersection Point

Here is a sample dataset to describe the methods. It shows the information on the Demand and Quantity of Apple Products and Google Products in the market. The values are placed in the Cell range B6:C9 and E6:F9 respectively.

Excel Graph Intersection Point


Now, we will create a graph and find the intersection point between the two graphical lines.

1. Manually Solve System of Equation to Find Intersection Point in Graph

When you have a quite simple dataset, you can manually solve the graph equation. As a result, you will find the intersection point between the lines. Let’s see how it works.

  • In the beginning, select the Cell range B6:C9.
  • Then, go to the Insert tab and choose Scatter with Smooth Lines and Markers chart type from the Charts section.

Manually Solve System of Equation to Find Intersection Point in Graph

  • As a result, you will get a single line graph based on the values like this.

  • Now, right-click on the chart and choose Select Data from its Context Menu.

  • After this, select Add in the new Select Data Source window.

  • Following this, insert the Cell range E6:E9 as Series X values and F6:F9 as Series Y values.

Manually Solve System of Equation to Find Intersection Point in Graph

  • Lastly, press OK and you will get another line intersecting with the previous one in the graph.
  • From this graph, we can see that the lines have intersected in the coordinates (5,7).

Excel Graph Intersection Point

  • This is the easiest process to get intersection points. But if you have large and complex datasets, then the upcoming methods will be great for you.

Read More: How to Find Intersection of Two Lists in Excel (3 Easy Methods)


2. Calculate Graph Intersection Point with Excel Solver

In this segment, we will use the Excel Solver tool to calculate the graph’s intersection point. To do the task, follow the steps below.

  • In the beginning, select the values of Demand and Quantity of Apple Products.
  • Then, insert a Scatter chart from the Charts section in the Insert tab.

Calculate Graph Intersection Point with Excel Solver

  • After this, enable Trendline from the Chart Elements.

  • Following this, right-click on Trendline and select Format Trendline.

  • Then, enable the Display Equation on chart option under the Trendline Options.

Calculate Graph Intersection Point with Excel Solver

  • Similarly, follow the same steps for the Google Product’s Demand and Quantity.
  • As a result, you will get the following graph with equations.

Calculate Graph Intersection Point with Excel Solver

  • Now, based on the equations, we will find the intersection points.
  • For this, separate the Variables and Constant values like this.

  • Next, insert this formula in Cell F5 which is a coefficient of the 1st Basic Formula.

=E5*1+E6*1

Calculate Graph Intersection Point with Excel Solver

  • Then, insert this formula as a coefficient of the 2nd Basic Equation in Cell F6.

=E5*3+E6

Calculate Graph Intersection Point with Excel Solver

  • At this stage, open Solver under the Analyze section of the Data tab.

  • Accordingly, insert Cell F5 in the Set Objective box.
  • Then, select Value of and provide 12 as the value.
  • Along with it, insert the Cell range E5:E6 in the By Changing Variable Cells.
  • Following this, click on Add to go to the next step.

Calculate Graph Intersection Point with Excel Solver

  • Now, insert Cells F6 and G6 as the Cell Reference and Constraint respectively.
  • Also, select Equal (=) as the condition.

Calculate Graph Intersection Point with Excel Solver

  • As a result, you will find the condition in the Subject to the Constraints box.
  • Lastly, press Solve on the Solver Parameters window.

  • In the Solver Results window, select Keep Solver Solution and hit on OK.

  • Finally, you will get the coordinate values of the intersection point.

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


3. Combine INTERCEPT & SLOPE Functions to Show Graph Intersection Point

The combination of the INTERCEPT and SLOPE functions is a time saver to get intersection points. For this, go through these simple steps.

  • First, insert this formula in Cell C11.
=(INTERCEPT(C6:C9,B6:B9)-INTERCEPT(F6:F9,E6:E9))/((SLOPE(F6:F9,E6:E9)-SLOPE(C6:C9,B6:B9)))

Combine INTERCEPT & SLOPE Functions to Show Graph Intersection Point

In this formula, the INTERCEPT function retrieves the point of intersection where a regression line will go through the yaxis. In this case, the xaxis and yaxis values are known. Along with this, the SLOPE function determines the slope of the linear equation.
  • Then, press Enter and you will get the X Intercept value.

  • Afterward, type this formula in Cell C12.
=SLOPE(C6:C9,B6:B9)*C11+INTERCEPT(C6:C9,B6:B9)

Combine INTERCEPT & SLOPE Functions to Show Graph Intersection Point

  • Lastly, hit Enter.
  • That’s it, you will get the Y Intercept value as well.


4. Determine Graph Intersection Point with Goal Seek Feature in Excel

Goal Seek is also a very useful tool in Excel to find the graph intersection point. For this, follow the steps below.

  • In the beginning, make both equations equal based on the Y variable.
  • Then, move them to the left-hand side of the equation resulting in 0 on the right-hand side like this.

Determine Graph Intersection Point with Goal Seek Feature in Excel

  • Next, insert this formula in Cell E5 according to the new equation.
=12-E7-22+3*E7

Determine Graph Intersection Point with Goal Seek Feature in Excel

  • Afterward, hit Enter to see the preliminary value of the Equation.

  • Along with this, insert 0 as the Result according to the newly generated equation.

  • Next, go to the Data tab and select WhatIf Analysis.

  • Here, choose Goal Seek from the drop-down menu.

  • After this, determine the parameters in the Goal Seek dialogue box as shown below.

Determine Graph Intersection Point with Goal Seek Feature in Excel

  • Then, press OK.
  • Lastly, hit on OK again in the Goal Seek Status window to confirm the solution found.

  • That’s it, we have got our X Intercept in Cell E7.

  • Also, insert this formula in Cell E8 based on the 2nd Basic Equation.
=22-3*E7

Determine Graph Intersection Point with Goal Seek Feature in Excel

  • Finally, hit Enter to get the Y Intercept as well.

5. Use Intersect Operator to Show Intersection Point

In this final method, let’s use Intersect Operator to find intersection points from multiple columns and rows. When 2 ranges are separated by a Space in the formula, they create an Intersect Operator. For illustration, here is a sample dataset with the quantity of Apple Products, Google Products and Samsung Products in the Cell range B5:D9.

Use Intersect Operator to Show Intersection Point

Now, let’s find out the intersection point among them.

  • For this, simply insert this formula in Cell C10.
=B5:C8 B6:D6

  • Following this, press Enter.
  • That’s it, we have got our required intersection points all at once.

Note: If you are using any version other than Excel365 then after typing the code, press F9 on your keyboard. It will confine the values inside Curly Brackets ({ }). After this, press Enter to get the output. Avoiding this process will result in #VALUE! error.

Conclusion

That’s all for today. I hope this article on how to find graph intersection points in Excel was helpful to you. Here we tried to demonstrate the process with 5 useful methods. Let us know your feedback on this. Follow ExcelDemy for more tutorials.


Related Articles

Sanjida Mehrun Guria
Sanjida Mehrun Guria

Hello! Welcome to my blog. I have completed my Bachelor in Architecture from Ashanullah University of Science & Technology, Dhaka. I am a passionate, goal-oriented person with an immense thirst for knowledge and an attitude to grow continuously. Besides Architectural work, I find it very enthusiastic to work in Excel blogging. Exceldemy is a platform where I have got the opportunity to flourish my skill in Microsoft Excel and therefore, here I will be posting blogs related to it.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo