find two curve intersection realtime!

[Manhatan]

Hello all, hope the best.

I have two set arrays that I want to find intersection point of them. One way is to fit a trendline and find the equation for each. then solve the equations and find the intersection. Problem is that everytime that any of those arrays changed a new trendline need to be fit and its equations solved to find the intersection.

I am wondering if there is any other method that can find the intersection which is autoupdated?
thanks.

 

[Eric W]

Your proposed procedure sounds reasonable, and can be made to autoupdate. For example:

	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
Array 1	Array 2	Intersection
Known y's	Known x's	m1	b1	interpolated values	Known y's	Known x's	m2	b2	Interpolated values	X	Y

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]

[TD="align: center"]2[/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: right"]-5[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]4.8[/TD]
[TD="align: right"]-10.2[/TD]
[TD="align: right"]-5.4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]-4.82432[/TD]
[TD="align: right"]13.83784[/TD]
[TD="align: right"]9.013514[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]2.497613[/TD]
[TD="align: right"]1.788543[/TD]

[TD="align: center"]4[/TD]
[TD="align: right"]-2[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]5[/TD]
[TD="align: right"]2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4.189189[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]4.2[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]0[/TD]
[TD="align: right"]3[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-0.63514[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: right"]8[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]9[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-8[/TD]
[TD="align: right"]4[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-5.45946[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"]14[/TD]
[TD="align: right"]5[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]13.8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-14[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"]-15.1081[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]E3[/TH]
[TD="align: left"]=B3*$C$3+$D$3[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]L3[/TH]
[TD="align: left"]=I3*$J$3+$K$3[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]O3[/TH]
[TD="align: left"]=(K3-D3)/(C3-J3)[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]P3[/TH]
[TD="align: left"]=O3*C3+D3[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]C3:D3[/TH]
[TD="align: left"]{=LINEST(A3:A7,B3:B7)}[/TD]
[/TR]
[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]J3:K3[/TH]
[TD="align: left"]{=LINEST(H3:H7,I3:I7)}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]



Put the arrays in a3:b7 and h3:i7. Select cells C3:D3, enter the LINEST function, then confirm with Control+Shift+Enter. This gets both the m and b values with 1 function. Otherwise you can use =INDEX(LINEST(...),1) in C3 and =INDEX(LINEST(...),2) in D3. Repeat in J3:K3. The E3 and L3 formulas are just to see how close the trendline is. Then the intersection can be derived with a little algebra, formulas in O3 and P3.

Now you can change the values in either of the arrays and instantly get the intersection.

If the number of points in the arrays change, you can change the LINEST formula to:

=LINEST(OFFSET(A3,0,0,COUNTA(A3:A15)),OFFSET(B3,0,0,COUNTA(B3:B15)))

which will figure out how many points exist and only include those.

Hope this helps.