# find two curve intersection realtime!

#### Manhatan

##### New Member
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.

### Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

#### Eric W

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

ABCDEFGHIJKLMNOP
1Array 1Array 2Intersection
2Known y'sKnown x'sm1b1interpolated valuesKnown y'sKnown x'sm2b2Interpolated valuesXY
3-514.8-10.2-5.491-4.8243213.837849.0135142.4976131.788543
4-22-0.6524.189189
5634.203-0.63514
6849-84-5.45946
714513.8-146-15.1081
8

</tbody>
Sheet1

Worksheet Formulas
CellFormula
E3=B3*\$C\$3+\$D\$3
L3=I3*\$J\$3+\$K\$3
O3=(K3-D3)/(C3-J3)
P3=O3*C3+D3

</tbody>

<tbody>
</tbody>

Array Formulas
CellFormula
C3:D3{=LINEST(A3:A7,B3:B7)}
J3:K3{=LINEST(H3:H7,I3:I7)}

</tbody>
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself

<tbody>
</tbody>

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.

Replies
5
Views
179
Replies
0
Views
238
Replies
2
Views
309
Replies
0
Views
658
Replies
7
Views
386

1,191,218
Messages
5,985,325
Members
439,958
Latest member
qb0000

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back