How to Find the Intersection of Two Curves in Excel?
Last Updated :
24 Jul, 2022
The intersection of the two curves is the point where the two curves meet, and their coordinates are the same. In this article, we will learn about how to find the intersection point of the two curves in excel. Finding the intersection point of two curves can be very useful in data analysis. Let's learn the steps for the same,
Intersection Point of two curves in Excel
Consider two sales curves of different regions Vs the year, and if they intersect at some point, then we would have insight at what time interval the sales of two different regions are matching or how close or how apart they are. For example, "Arushi" is a data analyst, and she notices that sales pattern 1 follows the curvey = x2 and sales pattern 2 follows the curvey = 2.7x. Her task is to find the intersection point of the two sales curves. Given a data set with x, y1, and y2 values, the formula used to calculate y1 values is by sales pattern 1, and the formula used to calculate y2 values is by sales pattern 2. A chart is also created.
Following are the steps,
Step 1: Create a new table range with headings x, y1, y2, and differences. Write any random value of x in cell D14. For example, 4.
Step 2: Write the same formula used in y1, as above, i.e.,y = x2. For example, =D14*D14. Press Enter.
Step 3: The value appears in cell E14. For example, 16.
Step 4: Write the same formula used in y2, as above, i.e.,Y = 2.7x. For example, =2.7^D14. Press Enter.
Step 5: The value appears in cell F14. For example, 53.1441.
Step 6: In the difference column, we are storing the difference between y1 and y2. This is done because we know that, at the intersection, the value y of both curves will be equal. This will be helpful in the later stages. Write =E14 - F14 is the cell G14. Press Enter.
Step 7: The value appears in cell G14. For example, -37.1441.
Step 8: Your active cell should be G14. Go to the Data tab, under the Forecast section, and click on the What-if Analysis. Click on the ribbon, and a drop-down appears. Select Goal Seek.
Step 9: Goal Seek dialogue box appears. In the set cell, select the cell G14, i.e., the difference column cell.
Step 10: We know that at the point of intersection, the difference between the y coordinates of the 2 curves is the same. So, it states that we need to put our difference value to zero(0).
Step 11: As all the formulas are dependent on the x value, we need to calculate the difference of 0 by changing the x value. So, select D14 in by changing Cell. Click Ok.
Step 12: The intersection point appears. The value of x is -0.70468, y1 is 0.49658, and y2 is 0.49662. The difference is 10-5, which tends to be 0.
Step 13: By graph also, we can observe that our values are correct.
Similar Reads
How to Iterate Across Columns to Find Intersection in R
In data analysis, it's common to work with datasets that contain multiple columns where you may want to find the intersection (common elements) across columns. R provides various ways to achieve this using vectorized operations, loops, or the apply family of functions using R Programming Language.Un
3 min read
How to Find the Intersection Between a Curve and Straight Line in Excel?
When two lines or curves having two different equations intersect with each other at one common point is known as Intersection. We can represent the point of intersection in Excel and also we can find the point of intersection using Excel functions SLOPE and INTERCEPT. Behind the intersection of a c
2 min read
How to Find the Percentage of Two Cells in Microsoft Excel?
If you want to find the percentage of two cells in Microsoft Excel, Simply select an empty cell, where you want to display the percentage of the two cells. Type in the following formula in the selected cell : =address of cell1/address of cell2 For example, I want to print out the percentage of C2 an
1 min read
How to Find the Y-intercept of a Line of a Graph in Excel?
A Y-intercept is a point on the line when x is equal to zero. It is more generally used in the slope-intercept form of the line. Let's learn, how to find the Y-intercept of a line of a graph in excel. Among many different forms of a line, the most commonly used line equation is the slope-intercept f
4 min read
How to Find Circular References in Excel
Circular Reference in Excel is like a loop that keeps going around in circles. It happens when a cell's formula depends on its own result, confusing Excel. In this article, you'll learn how to check, find, enable, or remove Find Circular References in Excel What is a Circular Reference in Excel?A ci
7 min read
How to Calculate the Interquartile Range in Excel?
In statistics, the five-number summary is mostly used as it gives a rough idea about the dataset. It is basically a summary of the dataset describing some key features in statistics. The five key features are :Minimum value: It is the minimum value in the data set.First Quartile, Q1: It is also know
3 min read
How to Find Range in Microsoft Excel
Understanding the range of a dataset is key to grasping data spread and is essential for interpreting and making data-driven decisions. Excel offers various methods to calculate ranges, enhancing your data analysis skills. This article explains the concept of range in statistics, explores Excel func
6 min read
Intersection of Two Linear Straight Lines in Excel
Intersection points can be beneficial in finding insights into the data, As intersections give the same values for different data sets. Excel can help to automate the task of finding the intersection point of two lines by using the =slope() and =intersection() function and replacing their values wit
4 min read
Most Efficient Way To Find The Intersection Of A Line And A Circle in Python
To make an efficient algorithm to find the intersection of a line and a circle, we need to understand what lines and circles are and how we can mathematically represent and calculate them in Python.PrerequisitesLineCirclesQuadratics EquationsSolving a Quadratic EquationQuadratic Formula/Sridharachar
8 min read
How to Compare Two Lists in Excel: 5 Easy Methods
Comparing two lists in Excel is a powerful way to identify matches, differences, or duplicates within your data. Whether youâre working with inventory records, customer databases, or project tasks, knowing how to compare two lists in Excel can save time and improve accuracy. Excel offers several too
7 min read