Rajiv3888 said:
assuming a graph sheet in which we have x = 10 & 20,
y1 = 11 & 19, y2 = 13 & 17, How to calculate the point
where y1 and y2 intersect in excel.
Download the example Excel "intercept of 2 lines.xls" from
https://www.box.com/s/3y53yoz0wekau2251jp5.
Suppose A2:A3 contain the x-coordinates 10 and 20, B2:B3 contain the
corresponding y-coordinates for line y1, 11 and 19, and C2:C3 contain the
corresponding coordinates for line y2, 14 and 17.
(Note: I changed 13 to 14 to avoid an interesting coincidence that might
mislead you. You can substitute 13 after you understand my example.)
Off-hand, I don't know of any Excel function that computes the interception
of the two lines. Instead, I rely on the following algebra.
y1 = m1*x + b1
y2 = m2*x + b2
The x-intercept for y1 = y2 is:
m1*x + b1 = m2*x + b2
so: x = (b2 - b1) / (m1 - m2)
In Excel, m is computed using SLOPE, and b is computed using INTERCEPT.
So the Excel formula for the x-intercept (in A6) is:
=(INTERCEPT(C2:C3,A2:A3)-INTERCEPT(B2:B3,A2:A3))
/(SLOPE(B2:B3,A2:A3)-SLOPE(C2:C3,A2:A3))
The Excel formula for the y-intercept (in C6 and D6) is:
C6: =FORECAST(A6,B2:B3,A2:A3)
D6: =FORECAST(A6,C2:C3,A2:A3)
Of course, C6 should equal D6, more or less.
(By coincidence, the y-intercept equals the x-intercept when y2 is defined
y=13 and y=17.)
