Results 1 to 2 of 2

Thread: Need help to locate point of intersection in two line graph in Excel

  1. #1
    Join Date
    Feb 2012
    Posts
    11

    Need help to locate point of intersection in two line graph in Excel

    I have a plot of two lines using excel which intersect at a point. I want to locate the point of their intersection using excel and show the co-ordinates of that point so any body can see i can read that point from it. Step by step procedure would be helpful and i am using Microsoft excel 2007. Thanks for any help

  2. #2
    Join Date
    Nov 2010
    Posts
    422

    Re: Need help to locate point of intersection in two line graph in Excel

    I will assume you have two straight lines - if not please tell me what they are. Let the lines follow the two functions : y1=m1x1+b1 and y2=m2x2+b2. Let the point of intersection be Y and X. Then m1X+b1=m2X+b2, giving X=(b2-b1)/(m1-m2). In Excel terms, X=(INTERCEPT(y-values2,x-values2)-INTERCEPT(y-values1,x-values1))/(SLOPE(y-values1,x-values1)-SLOPE(y-values2,x-values2)) and Y =SLOPE(y-values1,x-values1)*X+INTERCEPT(y-values1,x-values1)
    • Put these two formulas in cell A100 and B100
    • Select A1:B100 and Copy
    • Click chart; use Edit |Paste Special| specify New Series with X-value in first cell. Click OK Now the chart has the point of intersection as a marker.

    Hope this helps

Similar Threads

  1. Replies: 1
    Last Post: 25-02-2012, 11:31 AM
  2. Replies: 8
    Last Post: 07-02-2012, 07:59 PM
  3. How can I add a new line in Excel 2007 Graph
    By ashwin2012 in forum MS Office Support
    Replies: 2
    Last Post: 03-02-2012, 06:02 PM
  4. When is a line graph used?
    By Bohnieta in forum Off Topic Chat
    Replies: 4
    Last Post: 02-01-2012, 10:44 PM
  5. Cell intersection lookup in excel
    By Sayam in forum Windows Software
    Replies: 3
    Last Post: 30-07-2009, 10:51 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Page generated in 1,711,625,493.85323 seconds with 18 queries