Graph Intercepts?

[g.tavy]

Hello everyone,

I am wondering how to find the x intercepts of two simple line graphs (y=mx+c). I have already reshearched it and found a great help in lesson 263 at the youtube excel classes. But this lesson did not show how to find and display the intercepts of the two lines. I know that you have to make one function = the other and slove for x but how do you do it?

If you can help please?
Thanks
g.tavy.

 

[PA HS Teacher]

Haven't Looked at the lesson, but you can get the slope and Yintercepts directly from the data using the LINEST Function.

Assuming Y1=M1X+B1, and Y2=M2X+B2, Setting Y1=Y2 and solving for X yields,
X=(B2-B1)/(M1-M2)

To Find Y, where the two line intersect, substitute the value of X into either Y equation.

Assuming you have X data in column A, Y1 data in Column B, and Y2 data in Column C.

For X Enter:
=(INDEX(LINEST(C2:C7,$A2:$A7),2)-INDEX(LINEST(B2:B7,$A2:$A7),2))/(INDEX(LINEST(B2:B7,$A2:$A7),1)-INDEX(LINEST(C2:C7,$A2:$A7),1))

For Y Enter:
=INDEX(LINEST(B2:B7,$A2:$A7),1)*(INDEX(LINEST(C2:C7,$A2:$A7),2)-INDEX(LINEST(B2:B7,$A2:$A7),2))/(INDEX(LINEST(B2:B7,$A2:$A7),1)-INDEX(LINEST(C2:C7,$A2:$A7),1))+INDEX(LINEST(B2:B7,$A2:$A7),2)

 

[shg]

Nvm ...

 

[diddi]

you are asking 2 different questions at once.

do you want the intercepts of each function

or

do you want the x value of the intersection of the 2 lines


for intersection, x coordinate of the intersection of
y = m1 x + c1 and
y = m2 x + c2


x (at intersection) = (c2 - c1)/(m1 - m2)
y (at intersection) = m1 * x + c1



if you want x-intercepts, they are
x = -c1/M1 and -c2/M2

 

[g.tavy]

you are a GOD thank you that worked like a treat...
Thanks,,,

="GOD"

Just a little extra now... Please... the graph doen't seem to marry up to those intercepts any idea on how to get the graph accurate.

Thanks...

 

[PA HS Teacher]

What I posted will give where the two fit equations intersect, but the actual lines will not necessarily intersect at that location.

To get the point where with the two closes data points, I would add a 4th column, =Abs(C2-B2) pasted down.

=Index(A2:A7,Match(Min(Abs(C2-B2)),D2:D7,0))

Untested.