View original (French)

Intersection of 2 curves

Solved
MarcD1 Posted messages 5 Status Member -  
Raymond PENTIER Posted messages 58216 Registration date   Status Contributor Last intervention   -
Hello,
I am looking for an EXCEL sheet that would calculate the intersection between 2 curves (X,Y), even if this intersection is in the extension of the curves (meaning the extensions of the curves would be calculated), and would return the coordinates (X,Y) of the intersection point. There should also be an error message if the 2 curves cannot intersect.

Configuration: Macintosh / Safari 12.0.2

5 answers

  1.
    Raymond PENTIER Posted messages 58216 Registration date   Status Contributor Last intervention   17 482
     
    Hello.

    Question too general.
    Are your curves defined by functions or drawn point by point like successive measurements?

    --
    Retirement is great! Especially in the Antilles...
    Raymond (INSA, AFPA)
    7
    1. MarcD1 Posted messages 5 Status Member
       
      They are curves drawn point by point, like for successive measurements.
      0
  2. Raymond PENTIER Posted messages 58216 Registration date   Status Contributor Last intervention   17 482
     
    So, to be very clear:
    - if the curve is defined by points, as you confirm in post #2, there is only the method I indicated to you.
    - if the curves are representative of algebraic functions, the calculation is straightforward by writing y1=y2 and finding the value of x that satisfies this equality, even if the intersection is outside the bounds of the graph; we can also display "no intersection" if the curves are parallel or diverging.

    --
    Retirement is great! Especially in the Caribbean...
    Raymond (INSA, AFPA)
    2
    1. MarcD1 Posted messages 5 Status Member
       
      Thank you for the response. But it's not that simple, the two curves do not have the same values of X, which means that for (X1,Y1) & (X2,Y2), X1 > < X2 and Y1 > < Y2, and moreover, we are not sure that the intersection, if it exists, is within the measured data range for each of the curves (X1,Y1) and (X2,Y2), hence my question. Of course, we can simulate each curve with an equation and vary X1 = X2 with these 2 equations and proceed as you described, but if the 2 curves do not intersect we will have a random result, hence a second difficulty if I want to automate the EXCEL sheet.
      0
    2. Raymond PENTIER Posted messages 58216 Registration date   Status Contributor Last intervention   17 482
       
      I did my best with the few pieces of information provided.
      Working without the relevant file is inherently random. 
       1) Go to https://www.cjoint.com/ 
       2) Click on [Browse] to select your file (max 15 MB)
       3) Scroll down to click on the blue button [Create Cjoint link] 
       4) After a few seconds, the second page will display, with the link in bold; right-click on it and choose "Copy link"
       5) Go back to your discussion on CCM, and in your message, do "Paste".
      =>See the guide https://www.commentcamarche.net/faq/29493-utiliser-cjoint-pour-heberger-des-fichiers
      0
    4. Raymond PENTIER Posted messages 58216 Registration date   Status Contributor Last intervention   17 482
       
      Your 2 curves have become 5 curves!
      It's no longer the same problem at all...

      Personally, I see no solution other than VBA!
      0
  3. Raymond PENTIER Posted messages 58216 Registration date   Status Contributor Last intervention   17 482
     
    So:
    - in column A, the x coordinates
    - in column B, the y coordinates of the curve y1
    - in column C, the y coordinates of the curve y2
    - in column D, the difference y2-y1
    - in E2, the formula =SIGN(D2)-SIGN(D3)
    When column E shows -1, it means we have reached the intersection.
    It's nice, retirement! Especially in the Caribbean...
    Raymond (INSA, AFPA)
    0
  4. michel_m Posted messages 18903 Registration date   Status Contributor Last intervention   3 320
     
    Hello,
    a hefty example (author Andy Pope): with several intersections and marked with small circles
    https://mon-partage.fr/f/iflwdQLW/

    0
  6. MarcD1 Posted messages 5 Status Member
     
    Thank you very much.
    0