Mehedi Hasan
Fortunately, many of us use Excel in our business organizations. In any business organization, we use Excel to organize data as per need and make databases for the future. Moreover, one interesting thing is that we can find the intersection of two curves easily in Excel for better representation. However, I have used Microsoft Office 365 for the purpose of demonstration, and you can use other versions according to your preferences. In this article, I will show you a step-by-step procedure to find the intersection of two curves in Excel. Hence, read through the article to learn more and save time.
Download Practice Workbook
You can download the workbook used for the demonstration from the download link below.
Step-by-Step Procedure to Find Intersection of Two Curves in Excel
Often, we need to find the intersection of two curves in certain business analytics, and the process becomes more interesting with Excel. However, the task is easy and simple. But you will need an arrangement in order to perform the operation properly. Hence, go through the following steps to find your desired intersection of two curves using Excel.
📌 Step 1: Select Dataset
- For the purpose of demonstration, I have selected the following sample dataset. Here, the dataset contains 3 variables including X, Y1, and Y2.
- Here, we have selected 2 sample equations in order to plot equations in Excel.
y = 3x2 + 5x - 1
y = x3
- Now, select columns X and Y1.
- Then, click the Insert tab. Moreover, click on the Scatter command from the Charts group of commands.
- Next, select the Scatter chart. After some modification, it will look like the image below.
- Afterwards, select the graph to Select Data and click on Add.
- Now, provide the X and Y values and press OK.
- Finally, the chart which includes the intersection of two curves will appear as below after some modifications.
Read More: How to Find Graph Intersection Point in Excel (5 Useful Methods)
📌 Step 2: Calculation to Get Intersection of Two Curves
- Firstly, select cell E5 and insert the following formula to get the difference between Y1 and Y2.
=C5-D5
- Secondly, hit the Enter key and utilize the AutoFill tool to the entire column.
- Finally, you will get all the differences.
Read More: Intersection of Row and Column in Excel is Called a Cell
📌 Step 3: Find Intersection Point in Excel
- Initially, select any cell within range E5:E11.
- Then, go to the Data tab and select Forecast.
- Next, select Goal Seek from the What-If Analysis drop-down menu.
- After that, put 0 in To value. As there is no difference in the Y value of the two curves at the point of intersection.
- Again, select the B8 cell in By changing cell because you have to make the difference 0 by changing the value of X.
- Now, press OK.
- Lastly, it will show that it has found a solution and press Ok.
Read More: How to Find Intersection of Two Lists in Excel (3 Easy Methods)
Final Output
Last but not least, you will get your desired point of intersection of two curves. However, it will appear on the same row that you have selected in the Goal Seek dialog box. Here, I have found the value of X = 4.15 and Y = 71.34.
Here, the difference is less than 10-5 and it tends to be 0. Hence, the solution is correct enough.
However, you can check the value with the graph. From, the graph, it is clearly evident that the point of intersection of the two curves is correct.
💬 Things to Remember
- First of all, there is no direct chart option to get the intersecting point of two curves.
- Then, you can modify the chart according to your personal preference.
- However, you can choose any cell from the Difference(Y1-Y2) range and it will provide the same solution every time.
- At last, you can copy a row from the dataset and paste it to a different region in the sheet if you don’t want to overlap the solution with the dataset.
Conclusion
These are all the steps you can follow to find the intersection of two curves in Excel. Overall, in terms of working with time, we need this for various purposes. I have shown multiple methods with their respective examples, but there can be many other iterations depending on numerous situations. Hopefully, you can now easily create the needed adjustments. I sincerely hope you learned something and enjoyed this guide. Please let us know in the comments section below if you have any queries or recommendations.
For more information like this, visit Exceldemy.com.
