How To Find Intersection Of Two Lines In Excel

Finding the intersection of two lines is a fundamental concept in algebra and geometry, and it has practical applications in many fields, from engineering to finance. In Excel, finding the intersection of two lines can be especially useful for analyzing trends in data and making predictions based on those trends.

Excel offers a variety of tools and functions for solving mathematical problems, and finding the intersection of two lines is no exception. Whether you prefer to use built-in functions or write your own formulas, there are several methods for finding the intersection of two lines in Excel, each with its own strengths and weaknesses.

In this article, we’ll explore some of the most effective and efficient methods for finding the intersection of two lines in Excel. We’ll provide step-by-step instructions and example data for each method, so you can choose the one that best fits your needs and skill level. By the end of this article, you’ll have a solid understanding of how to find the intersection of two lines in Excel, and you’ll be ready to apply this knowledge to your own data analysis and modeling projects.

Let’s learn it with the help of the following data set shown above.

Method 1 – Manually Solve Equations To Show Intersection Point

We will use a manual visual aid to illustrate the intersection points between two lines. Despite its limitations, this method is suitable for quick and moderately reliable results. Learn how to show the intersection point in an Excel graph using the following steps.

Step 1 – Insert Scatter Chart

• Select your first table. Go to Insert Tab, under Chart group click on the Scatter Chart button & select Scatter Chart With Line & Markers.
• You will notice a chart will be plotted on your sheet.

Step 2 – Add Second Table Into Your Chart

• To add data from the second table in your chart, right click on the chart & click on the select data.

Step 3 – Select Data Source

• Click on the Add button.

Step 4 – Select Data Range

• Drag your mouse over columns & click the OK button.

Step 5 – Second Line Inserted

• A second line will be inserted in your chart.
• Go on & edit the chart title, call data points & place markers.
• Your chart should look like this.
• The intersection point of these two lines is 5, 7.

Step 6 – Intersection Point Discovered

• With this simple method, you can find out the intersection of two lines.

Method 2 – Display Intersection Point Through Excel Solver Tool

The Solver tool is an extremely powerful tool that can solve a variety of problems quickly and efficiently. Among them is solving equations. Here, we will solve two equations using the Solver tool to determine where their intersections lie.

Step 1 – Prepare Equation

• For this method we will prepare the equation first then we will find out the X & Y values. Below is the table for this example.

Step 2 – Enter Formula

• In Cell D2, enter the formula: =C2*1+C3*1.

Step 3 – Add Another Formula

• In Cell D3, enter the formula: =3*C2+C3.

Step 4 – Go To The Data Tab

• Now go to the Data Tab, under Analyze group click on the Solver button.

Step 5 – Add Solver Parameters

• When you click on the Solver button, the Solver Parameter window will appear on your screen. Let’s add the parameters.
• Select cell D2 in the Set objective tab.
• Next choose the condition as Value of & type 12.
• Then select cell C2:C3 in the By Changing Variable Cells tab.
• Click Add button to Add Constraints.

Step 6 – Add Constraints

• Select cell D3 as Cell Reference.
• Choose equals to (=) sign from drop down list.
• Select cell E3 as Constraint.
• Click the OK button.

Step 7 – Solver Parameters Completed

• When you click on the OK button, you will be returned back to the Solver Parameters screen.
• All the parameters have been set. Click the Solve button.

Step 8 – Solver Result

• When you click on the Solve button. The Solver results window will appear on your screen. Click the OK button to have your X & Y values cell C2 & C3 respectively.

Step 9 – Intersection of Two Lines Discovered

• With the help of the Solver tool we can find out the intersection points of two lines.