Intersection of two curves

  • Thread starter Thread starter Harish
  • Start date Start date
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
 
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
Click to expand...

You do not have curves in Excel, only lines between points - or curve alike
interpolations.

So simply you set y1=n1+m1*x1 and y2=n2+m2*x2 (get the n and the m first by
using the neighbouring x-y-coordinates of each of the two lines) to

n1+m1*x1 = n2+m2*x2 :X1=x2!
(n1-n2)/x = m2-m1
and get x and y with:
x = (n1-n2)/(m2-m1)
y=n1+m1*x
 
One approach based on polynomial curves...
Suppose the two sets of points are in A1:B4 and D1:E4 as
below. From the chart, the intersection lies between 4 and 6.

0,2 | 0,0
2,5 | 4,2
7,6 | 6,8
9,8 | 9,9

1. For a linear fit through neighboring points the difference is:

=TREND(E2:E3,D2:D3,G1)-TREND(B2:B3,A2:A3,G1)

which can be set to zero by changing G1. Goal seek gives (5.214,5.643).
Alternatively you can use: -(slope2-slope1)/(intercept2-intercept1).

2. For a cubic fit through all four points the difference is:

=TREND(E1:E4,D1:D4^{1,2,3},G1^{1,2,3})
-TREND(B1:B4,A1:A4^{1,2,3},G1^{1,2,3})

Using goal seek, or via the formula below, we find (5.195,5.711).

=IRR(LINEST(E1:E4,D1:D4^{1,2,3})-LINEST(B1:B4,A1:A4^{1,2,3}))*1+1

HTH. Lori
 
Hello,

I've tried using this formula (=IRR(LINEST...) in my task. However, it only returned one value instead of two or more intersection values. I used two sets of data to form f(x) and g(x) which both were using 5th order polynomials. Anybody please advise me. Thank you.
 
Hello,

I've tried using this formula (=IRR(LINEST...) in my task. However, it only returned one value instead of two or more intersection values. I used two sets of data to form f(x) and g(x) which both were using 5th order polynomials. Anybody please advise me. Thank you.






- Show quoted text -
Click to expand...

Generate the composite polynomial (f(x) - g(x)) and differentiate.
Search for the points of inflection i.e. dy/dx = 0 ... these are the
points of intersection of your original functions.

A.
 
Back
Top