Intersection of two curves
On Sat, 01 Aug 2009 07:33:37 GMT, Lars-Åke Aspelin
wrote:
On Fri, 31 Jul 2009 23:30:50 -0700 (PDT), Harish
wrote:
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
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
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
|