|  | Determine Intersection of 2 lines using Excel VBA | | |
| | | Guest |  |
|  Posted: Sat Aug 30, 2008 10:56 pm Post subject: Determine Intersection of 2 lines using Excel VBA |  |
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 |
| |
| | | Dana DeLouis | |
| | Posted: Sun Aug 31, 2008 7:35 pm Post subject: Re: Determine Intersection of 2 lines using Excel VBA | |
| |  | |
| Quote: | 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.
|
If we "assume" a linear equation for both, I think this will do what you want.
Sub Demo()
'/ Input data range
Dim xs, ys1, ys2
'// Slope & Intercept of both lines
Dim s1, s2, int1, int2
'// solution
Dim X, Y
Set xs = [A2:W2]
Set ys1 = [A5:W5]
Set ys2 = [A10:W10]
With WorksheetFunction
s1 = .Slope(ys1, xs)
int1 = .Intercept(ys1, xs)
s2 = .Slope(ys2, xs)
int2 = .Intercept(ys2, xs)
X = (int2 - int1) / (s1 - s2)
Y = X * s1 + int1
End With
MsgBox "X: " & X & vbLf & "Y: " & Y
End Sub
--
HTH :>)
Dana DeLouis
<velocityinc@gmail.com> wrote in message news:92987514-69be-41ff-be4b-bbdd7707af89@k13g2000hse.googlegroups.com...
| Quote: | 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 |
|
| |
|
|
