Interception point of 2 curves with 2 different sets of data (x1,y1) (x2,y2).

Hello Everyone,

I am new to the forum, so please accept my apologies if I am not posting on the correct section.

I am working on a Scatter chart that has 2 sets of data (x1,y1) and (x2,y2). At some point this 2 curves will meet. I have used google, youtube and all forums (including this one, but couldn't find any solution for this (I must confess that don't really know if this is possible.

So basically these 2 curves will be represented by for instance:

First Set of data:

x2 y2
0.06 1.5
0.09 3.375
0.12 6
0.15 9.375
0.18 13.5
0.21 18.375
0.24 24
0.27 30.375
0.3 37.5
0.33 45.375
0.36 54
0.39 63.375
0.42 73.5
0.45 84.375
0.48 96
0.51 108.375
0.54 121.5
0.57 135.375
0.6 150
0.63 165.375
0.66 181.5
0.69 198.375
0.696 201.84
0.72 216
0.75 234.375
0.78 253.5
0.81 273.375
0.84 294
0.87 315.375
0.9 337.5
0.93 360.375
0.96 384
0.99 408.375
1.02 433.5

Second set of data:

x1 y2
0.00 947
0.10 878
0.20 797
0.30 716
0.40 613
0.50 491
0.60 331
0.70 110
0.80 -93
0.85
0

Now, by looking at the scattered graph, where I can see the 2 lines crossing, I can easily see that both curves will meet each other on (0.665,184)

But, is there a way of finding this point by the use of some formulas?

I have attached an image showing these 2 lines.
image forum.PNG

Thank you very much for your help with this.

Kind regards,
Trevor

You need some moderately involved calculations.

If (x2,y2) including labels in top row were in A4:B38 and (x1,y1) including labels in left column were in B1:L2, then

A1:

Formula:

Please Login or Register  to view this content.

A2:

Formula:

Please Login or Register  to view this content.

C5:

Formula:

Please Login or Register  to view this content.

Copy C5, paste into C5:L38. That becomes an array of scaled distances between points in the respective data sets. Scaling is necessary given the differences in scale between x and y coordinates.

The smallest difference corresponds to (0.66,181.5) from the 1st data set and (0.7,110) from the 2nd data set. You'd need to complete a rectangle with (0.69,198.375) from the 1st data set (point with x-axis value closer to the 2nd data set base point) and (0.6,331) from the 2nd data set (point with x-axis value closer to the previous 1st data set base point). You could then just use the intersection of the segments from (0.66,181.5) to (0.69,198.375) and from (0.6,331) to (0.7,110). The 1st is given by y = 562.5 x - 189.75 and the 2nd by y = -2210 x + 1657, where m₁ = 562.5 = (198.375 - 181.5) / (0.69 - 0.66), b₁ = -189.75 = 181.5 - m₁ 0.66, m₂ = -2210 = (110 - 331) / (0.7 - 0.6), and b₂ = 1675 = 331 - m₂ 0.6. The intersection is given by x_i = (b₂-b₁)/(m₁-m₂) = 0.666095582 and y_i = m₁ x_i + b₁ = m₂ x_i + b₂ = 184.9287647.

This is approximate, but it may be sufficient for your purposes.

Given the C5:L38 grid, the row index of the minimum value is given by the array formula

C41:

Formula:

Please Login or Register  to view this content.

and the column index by the array formula

C42:

Formula:

Please Login or Register  to view this content.

The 4 points are then given by

D41:

Formula:

Please Login or Register  to view this content.

E41:

Formula:

Please Login or Register  to view this content.

D42:

Formula:

Please Login or Register  to view this content.

E42:

Formula:

Please Login or Register  to view this content.

D44:

Formula:

Please Login or Register  to view this content.

E44:

Formula:

Please Login or Register  to view this content.

D45:

Formula:

Please Login or Register  to view this content.

E45:

Formula:

Please Login or Register  to view this content.

Slopes and intercepts by

F41 (m₁):

Formula:

Please Login or Register  to view this content.

F42 (b₁):

Formula:

Please Login or Register  to view this content.

F44 (m₂):

Formula:

Please Login or Register  to view this content.

F45 (b₂):

Formula:

Please Login or Register  to view this content.

and the intersection point by

D47 (x_i):

Formula:

Please Login or Register  to view this content.

E47 (y_i calculated with m₁ and b₁):

Formula:

Please Login or Register  to view this content.

F47 (y_i calculated with m₂ and b₂):

Formula:

Please Login or Register  to view this content.