How to calculate the coordinates of intersection points of scatter plot curves in Excel

php editor Zimo introduces to you the method of calculating the coordinates of the intersection point of the scatter plot curve in Excel. In data analysis, scatter plots and curve charts are often used to represent relationships, and intersection coordinates can help us understand the relationship between data more intuitively. With the following method, you can easily calculate the intersection coordinates of scatter plots and curve plots, providing deeper insights for data analysis.

1. There are current curves with different loads when the elevator is running. The current value is the Y-axis, the load-to-load ratio is the X-axis, and the current of 30%, 40%, 45%, 50%, and 60% of the load when the elevator is going up. Generate a curve. When the elevator goes down, the current of 30%, 40%, 45%, 50%, and 60% load generates a curve. Then the two curves will have an intersection point. The X-axis of the intersection point needs to be calculated in the Excel cell. value. As shown in the figure: Calculate the x-axis value of the intersection point.

2. From the figure, you can intuitively see that the intersection point is between the loads (40-50) - the yellow area in the above figure. Keep the data in the yellow area in the red box. , and other deletions. This step is to lock a relatively small range and reduce the difficulty of solving; the deleted graph is as follows:

3. Add a fitting curve (actually it is also based on higher-order equations) (to solve based on the principle of point fitting), the adding method is as shown in the figure:

4. Add a trend line, select a polynomial, and display the formula. The purpose is to use polynomials to fit the original curve.

5. In the same way, add the polynomial trend to another line and display the formula.

6. At this time, you can get a system of equations: y = 0.2x - 6...(1)y = 0.02x^2 - 2x 52...(2) Use excel By solving the system of equations (1) (2), the intersection position of the fitting curve can be calculated, which is the [balance coefficient].

2. Solve the system of equations in Excel

1. The system of equations (1)(2) is organized into equations: 0.02x2 - 2.2x 58 = 0

2. Select In cell B4, enter X in the name box (note the [name box] in the upper left corner), as shown in the figure:

3. Enter the formula =0.02*X^ in cell B5 2-2.2*X58.

4. Solve

Target cell: B5

Target value: 0

Variable cell: B4

Click [OK] and the balance coefficient is 43.82

The above is the detailed content of How to calculate the coordinates of intersection points of scatter plot curves in Excel. For more information, please follow other related articles on the PHP Chinese website!