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. If anyone could please help with a formula to use in excel i will be highly obliged.

Regards,
Rajiv Bhasin.

"Rajiv3888" wrote:
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.)