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

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.

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.

• 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.

• 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).

• 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.

• 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.

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

• 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.

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

• 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.

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

• 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.

• Then, press Enter and you will get the X Intercept value.

• Afterward, type this formula in Cell C12.

• 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.

• Next, insert this formula in Cell E5 according to the new equation.

• 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 What–If Analysis.

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

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

• 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.

• 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.

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

• For this, simply insert this formula in Cell C10.

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

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

• Performing Intersection of Two Data Sets in Excel (4 Easy Ways)