determining the intersect of 2 lines using vba

[velocityinc]

Y values - I have y-values in 2 rows of my spreadsheet, say A5:W5 and
A10:W10.
X values - The x-values are common to both rows, and listed in A2:W2.
I would like to use vba to determine the intersection of these y-
values, and determine where on the y-axis and x-axis do the lines
intersect.
I would like for the intercept to show up in a message box.
Could someone please assist with some vba code on this "intercept
analysis"
Thanks in advance.
V

 

[smartin]

Y values - I have y-values in 2 rows of my spreadsheet, say A5:W5 and
A10:W10.
X values - The x-values are common to both rows, and listed in A2:W2.
I would like to use vba to determine the intersection of these y-
values, and determine where on the y-axis and x-axis do the lines
intersect.
I would like for the intercept to show up in a message box.
Could someone please assist with some vba code on this "intercept
analysis"
Thanks in advance.
V

V,

I bookmarked your same question elsewhere... please refrain from
multiposting. See, now your question is here and there. I am here;
others may be there. Confusing, eh? Yes, yes it is.

Anyway, I have some questions about your data.

What is the purpose of having so many data points? Two points determine
a line, you know. Are these two "lines" or two "jaggety lines"? IOW, can
there be multiple intersects?

 

[smartin]

V,

I bookmarked your same question elsewhere... please refrain from
multiposting. See, now your question is here and there. I am here;
others may be there. Confusing, eh? Yes, yes it is.

Anyway, I have some questions about your data.

What is the purpose of having so many data points? Two points determine
a line, you know. Are these two "lines" or two "jaggety lines"? IOW, can
there be multiple intersects?

Hi V,

You responded (privately)

"Some curve could exist. However, the lines will not cross more than
once - no multiple intersects, only one."

First off, I have no VBA solution, though someone could probably figure
that out. I did come up with a worksheet solution, which consists of
these crazy formulae:

In B13:W13, compute the "fragment" slopes of line 1:
=(B5-A5)/(B2-A2)

In B14:W14, compute the "fragment" y-intercepts of line 1:
=INTERCEPT(A5:B5,A2:B2)

Similarly for line 2 in rows 17 and 18:
[slopes] =(B10-A10)/(B2-A2)
[intercepts] =INTERCEPT(A10:B10,A2:B2)

Now compute the intersections of the "fragment" lines in rows 21 and 22:
[x] =(B18-B14)/(B13-B17)
[y] =(B17*B14-B13*B18)/(B17-B13)

Finally, figure out which of the "fragment" intersects is the real one
in another row:
=AND(B21<B2,B21>A2)

I did not test this extensively, but it works with random sample data
conforming to your description.