Intersection of two curves

H

Harish

Hi,

I have two curves plotted in excel using the data points and these two
curves intersect. I want to find the intersection coordinates of these
2 curves. How do I do that? Thanks in advance

Swamy
 
L

Lars-Åke Aspelin

Hi,

I have two curves plotted in excel using the data points and these two
curves intersect. I want to find the intersection coordinates of these
2 curves. How do I do that? Thanks in advance

Swamy
Click to expand...


Do the curves have the same X-values and different Y-values or do they
have different X-values as well?

Lars-Åke
 
L

Lars-Åke Aspelin

Hi,

I have two curves plotted in excel using the data points and these two
curves intersect. I want to find the intersection coordinates of these
2 curves. How do I do that? Thanks in advance

Swamy
Click to expand...

Assuming that your X-data are in cells A1:A10 and your Y-data are in
cells B1:B10 for the first curve and in C1:C10 for the second curve.

Try this formulas to find the coordinates for the first intersection.

For the X-coordinate:

=INDEX(A1:A10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))-1)+
(INDEX(A1:A10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)))-
INDEX(A1:A10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))-1))*
(INDEX(B1:B10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))-1)-
INDEX(C1:C10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))-1))/
(INDEX(B1:B10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))-1)-
INDEX(C1:C10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))-1)-
INDEX(B1:B10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)))+
INDEX(C1:C10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))))

For the Y-coordinate:

=INDEX(B1:B10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))-1)+
(INDEX(B1:B10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)))-
INDEX(B1:B10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))-1))*
(INDEX(B1:B10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))-1)-
INDEX(C1:C10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))-1))/
(INDEX(B1:B10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))-1)-
INDEX(C1:C10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))-1)-
INDEX(B1:B10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)))+
INDEX(C1:C10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))))

Note: These are array formulas that have to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER

Change the ranges to fit the size of your data.

Hope this helps / Lars-Åke
 
L

Lars-Åke Aspelin

Assuming that your X-data are in cells A1:A10 and your Y-data are in
cells B1:B10 for the first curve and in C1:C10 for the second curve.

Try this formulas to find the coordinates for the first intersection.

For the X-coordinate:

=INDEX(A1:A10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))-1)+
(INDEX(A1:A10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)))-
INDEX(A1:A10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))-1))*
(INDEX(B1:B10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))-1)-
INDEX(C1:C10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))-1))/
(INDEX(B1:B10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))-1)-
INDEX(C1:C10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))-1)-
INDEX(B1:B10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)))+
INDEX(C1:C10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))))

For the Y-coordinate:

=INDEX(B1:B10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))-1)+
(INDEX(B1:B10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)))-
INDEX(B1:B10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))-1))*
(INDEX(B1:B10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))-1)-
INDEX(C1:C10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))-1))/
(INDEX(B1:B10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))-1)-
INDEX(C1:C10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))-1)-
INDEX(B1:B10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)))+
INDEX(C1:C10,INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))))

Note: These are array formulas that have to be confirmed with
CTRL+SHIFT+ENTER rather than just ENTER

Change the ranges to fit the size of your data.

Hope this helps / Lars-Åke
Click to expand...

Ooops, that was a few INDEX too many. Try this instead

For the X-coordinate:

=INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)-1)+
(INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))-
INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)-1))*
(INDEX(B1:B10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)-1)-
INDEX(C1:C10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)-1))/
(INDEX(B1:B10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)-1)-
INDEX(C1:C10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)-1)-
INDEX(B1:B10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))+
INDEX(C1:C10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)))

For the Y-coordinate:

=INDEX(B1:B10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)-1)+
(INDEX(B1:B10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))-
INDEX(B1:B10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)-1))*
(INDEX(B1:B10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)-1)-
INDEX(C1:C10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)-1))/
(INDEX(B1:B10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)-1)-
INDEX(C1:C10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)-1)-
INDEX(B1:B10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))+
INDEX(C1:C10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)))

Still array formulas. Confirm with CTRL+SHIFT+ENTER.

Hope this helps / Lars-Åke
 
L

Lars-Åke Aspelin

Ooops, that was a few INDEX too many. Try this instead

For the X-coordinate:

=INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)-1)+
(INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))-
INDEX(A1:A10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)-1))*
(INDEX(B1:B10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)-1)-
INDEX(C1:C10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)-1))/
(INDEX(B1:B10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)-1)-
INDEX(C1:C10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)-1)-
INDEX(B1:B10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))+
INDEX(C1:C10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)))

For the Y-coordinate:

=INDEX(B1:B10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)-1)+
(INDEX(B1:B10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))-
INDEX(B1:B10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)-1))*
(INDEX(B1:B10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)-1)-
INDEX(C1:C10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)-1))/
(INDEX(B1:B10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)-1)-
INDEX(C1:C10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)-1)-
INDEX(B1:B10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0))+
INDEX(C1:C10,MATCH(-SIGN(B1-C1),SIGN(B1:B10-C1:C10),0)))

Still array formulas. Confirm with CTRL+SHIFT+ENTER.

Hope this helps / Lars-Åke
Click to expand...

And with named ranges, _X, _Y1, and _Y2 respectively:

For the X-coordinate:

=INDEX(_X,MATCH(-SIGN(OFFSET(_Y1,,,1)-OFFSET(_Y2,,,1)),SIGN(_Y1-_Y2),0)-1)+
(INDEX(_X,MATCH(-SIGN(OFFSET(_Y1,,,1)-OFFSET(_Y2,,,1)),SIGN(_Y1-_Y2),0))-
INDEX(_X,MATCH(-SIGN(OFFSET(_Y1,,,1)-OFFSET(_Y2,,,1)),SIGN(_Y1-_Y2),0)-1))*
(INDEX(_Y1,MATCH(-SIGN(OFFSET(_Y1,,,1)-OFFSET(_Y2,,,1)),SIGN(_Y1-_Y2),0)-1)-
INDEX(_Y2,MATCH(-SIGN(OFFSET(_Y1,,,1)-OFFSET(_Y2,,,1)),SIGN(_Y1-_Y2),0)-1))/
(INDEX(_Y1,MATCH(-SIGN(OFFSET(_Y1,,,1)-OFFSET(_Y2,,,1)),SIGN(_Y1-_Y2),0)-1)-
INDEX(_Y2,MATCH(-SIGN(OFFSET(_Y1,,,1)-OFFSET(_Y2,,,1)),SIGN(_Y1-_Y2),0)-1)-
INDEX(_Y1,MATCH(-SIGN(OFFSET(_Y1,,,1)-OFFSET(_Y2,,,1)),SIGN(_Y1-_Y2),0))+
INDEX(_Y2,MATCH(-SIGN(OFFSET(_Y1,,,1)-OFFSET(_Y2,,,1)),SIGN(_Y1-_Y2),0)))

For the Y-coordinate:

=INDEX(_Y1,MATCH(-SIGN(OFFSET(_Y1,,,1)-OFFSET(_Y2,,,1)),SIGN(_Y1-_Y2),0)-1)+
(INDEX(_Y1,MATCH(-SIGN(OFFSET(_Y1,,,1)-OFFSET(_Y2,,,1)),SIGN(_Y1-_Y2),0))-
INDEX(_Y1,MATCH(-SIGN(OFFSET(_Y1,,,1)-OFFSET(_Y2,,,1)),SIGN(_Y1-_Y2),0)-1))*
(INDEX(_Y1,MATCH(-SIGN(OFFSET(_Y1,,,1)-OFFSET(_Y2,,,1)),SIGN(_Y1-_Y2),0)-1)-
INDEX(_Y2,MATCH(-SIGN(OFFSET(_Y1,,,1)-OFFSET(_Y2,,,1)),SIGN(_Y1-_Y2),0)-1))/
(INDEX(_Y1,MATCH(-SIGN(OFFSET(_Y1,,,1)-OFFSET(_Y2,,,1)),SIGN(_Y1-_Y2),0)-1)-
INDEX(_Y2,MATCH(-SIGN(OFFSET(_Y1,,,1)-OFFSET(_Y2,,,1)),SIGN(_Y1-_Y2),0)-1)-
INDEX(_Y1,MATCH(-SIGN(OFFSET(_Y1,,,1)-OFFSET(_Y2,,,1)),SIGN(_Y1-_Y2),0))+
INDEX(_Y2,MATCH(-SIGN(OFFSET(_Y1,,,1)-OFFSET(_Y2,,,1)),SIGN(_Y1-_Y2),0)))


As you notice the expression

=MATCH(-SIGN(OFFSET(_Y1,,,1)-OFFSET(_Y2,,,1)),SIGN(_Y1-_Y2),0)

is quite frequent in these formula. It represents the index of the
values for which the difference of the Y values have switched sign
(compared to the Y value difference for the first X-value)
If you place this formula, note this part is the array formula that
requires the CTRL+SHIFT+ENTER, in e.g. cell D1 you can reduce the
formulas as follows.

For the X-coordinate:

=INDEX(_X,D1-1)+(INDEX(_X,D1)-INDEX(_X,D1-1))*(INDEX(_Y1,D1-1)-INDEX(_Y2,D1-1))/
(INDEX(_Y1,D1-1)-INDEX(_Y2,D1-1)-INDEX(_Y1,D1)+INDEX(_Y2,D1))

For the Y-coordinate

=INDEX(_Y1,D1-1)+(INDEX(_Y1,D1)-INDEX(_Y1,D1-1))*(INDEX(_Y1,D1-1)-INDEX(_Y2,D1-1))/
(INDEX(_Y1,D1-1)-INDEX(_Y2,D1-1)-INDEX(_Y1,D1)+INDEX(_Y2,D1))

Hope this helps / Lars-Åke
 
L

Lori

fwiw, you could also try this ctrl+shift+entered:

=FORECAST(0,A1:A10,IF((ROW(A1:A10)-ROW(A1)-
MATCH(B1>C1,B1:B10<=C1:C10,0)+1.5)^2<1,C1:C10-B1:B10))

for X and replace A's with B's for Y. See other posting for alternatives.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Intersection of two curves 4
How do I plot two curves on a single XY scatter chart? 1
Intersection 3
Write to intersect of a row and column 7
Add vertical line at intersection of 2 curves 3
How do I partially fill a plot area with color? 4
Named ranges scope / workbook/worksheet level named ranges- changeswith variable use... 3
Excel Range calculation breaking application.calculate - horrifyingproblem 8

Top