Intersection of two lines

[LeeCC]

Dear friends:

I am having EXCEL2003.

Given two sets of points (2 points per set), I am able to draw two lines.
Unfortunately, the two lines do not intersect.

How can I EXTEND these lines so that I can read (or return by XL) the
intersection point off the chart?

Thanks.


Lee CC

 

[Bernard Liengme]

You could chart the two data series with only makers (no lines) then add
trendlines to each data series to see where they cross.

But it is easier to do it with math.
For each pair of data points, use SLOPE and INTERCEPT to find the slope and
intercept of the lines that passes thru them
Let these be m1,b1 and m2,b2
Let X,Y be the point of intersection. Since this point is on both lines
m1X+b1 = m2X+b2
So find X with X= (b2-b1)/(m1-m2)
Get Y with Y = m1X + b1

Send me a private email (remove TRUENORTH.) with the data and I will return
a workbook showing both methods
best wishes

 

[LeeCC]

Hello! Bernard

Thanks for the prompt reply and the earnest helps.

I can solve it with algebra. But the challenge to this exercise is to use
ONLY EXCEL to solve the intersection.

(note: I already added the trendline under the XY scatter plot.
Unfortunately the given points made the two lines too short to cut).


Lee CC
---------------------------------------------------------------

 

[Bernard Liengme]

But trendlines can be extended. Right click the trendline and select Format
Trendline; then on the Options tab look for 'extend forwards and backwards'

I was using Excel also (I was doing algebra with Excel - doesn't that count
for this homework?)
best wishes

 

[LeeCC]

Thank you. Manage to EXTEND the lines with your valuable advice.

Never expect "Forecast Forward" (in XL2003) means EXTENSION! My God!

Thanks again.


Lee CC
----------------------------------------------------------

 

[Shane Devenshire]

You can solve Bernard's formulas using Excel, you just need to write them
with cell addresses.

Also, there is a spreadsheet function called TREND which calculated a
trendline value in the spreadsheet. You might also look at the SLOPE,
INTERCEPT, and LINEST functions.