Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Intersection of two curves
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35
Default Intersection of two curves
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
Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 913
Default Intersection of two curves
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


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

Lars-Åke
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35
Default Intersection of two curves
yes the two curves share the same X values but have different Y values
Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 913
Default Intersection of two curves
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


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 913
Default 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

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 913
Default Intersection of two curves
On Sat, 01 Aug 2009 07:53:23 GMT, Lars-Åke Aspelin
wrote:

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

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
Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 35
Default Intersection of two curves
Wow......This really came to be useful. Thanks a lot for your help.

Swamy
Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 272
Default Intersection of two curves
fwiw, you could also try this ctrl+shift+entered:

=FORECAST(0,A1:A10,IF((ROW(A1:A10)-ROW(A1)-
MATCH(B1C1,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.

"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

Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
S Curves Marc Shields Excel Worksheet Functions 2 January 26th 10 03:03 AM
curves PAL Excel Worksheet Functions 2 June 25th 08 08:15 AM
How can I project the values of intersection of curves in chart on RahulMani Excel Worksheet Functions 1 June 5th 06 04:00 PM
Followup:Add vertical line at intersection of 2 curves (Ping AndyPope) LeAnne Charts and Charting in Excel 2 May 30th 06 08:28 PM
Add vertical line at intersection of 2 curves LeAnne Charts and Charting in Excel 3 May 28th 06 09:55 PM


All times are GMT +1. The time now is 10:46 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement