Intercept of two lines in excel chart

Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Intercept of two lines in excel chart

  1. #1
    New Member
    Join Date
    Aug 2002
    Location
    USA
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

     
    Does anybody know how to find the exact value of the intercept of two lines in excel chart. I can find the aprox. value but i need an exact one. I use office 97 ( i work for a not very technologically advanced company)...

    I would appreciate any help.

  2. #2
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    11,007
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default


    Do you know the equations of the two lines?

    If not, I assume you have a bunch of data points, and the question becomes how are you creating the lines? (a) with a trendline, (b) with XL's charting module connecting the dots, (c) with (b) and smoothing turned on.

    _________________
    Regards,

    Tushar
    www.tushar-mehta.com

    [ This Message was edited by: tusharm on 2002-08-09 12:30 ]

    [ This Message was edited by: tusharm on 2002-08-09 12:30 ]

  3. #3
    New Member
    Join Date
    Aug 2002
    Location
    USA
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I am using the chart wizard. Yes, i do have the data points that are connected into one line... any ideas?

  4. #4
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    11,007
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default

    OK, courtesy of some code I wrote some time ago, this task is not too difficult. However, do bear in mind that the user interface is not fool-proof. So, even coming from a technologically-challenged company you will have to do some work. Though, this is a non-programmatic solution so you don't have to write VBA code. Does that help?

    Note that the x-values for both data sets have to be in ascending order.

    Name (Insert | Name > Define..) the x-values of your first data set x1Vals. Name the y-values y1Vals. Similarly, name the x- and y-values of your 2nd data set x2Vals and y2Vals, respectively.

    In the Visual Basic Editor...oh, no! Wait, we are using a non-programmatic solution. Scratch that.

    Plot the two series so that you have an approximate idea of the x-values where the two intersect.

    In some cell, enter that approx. value. Name that cell CommonXVal.

    {Instead of typing the formulas below you may be better off with copy-paste steps}

    In two adjacent cells, say, J8 and K8, enter the formulas
    =MATCH(commonXVal,x1vals,1)

    and
    =MATCH(commonXVal,x2vals,1)

    In the two cells just below these (i.e., J9 and K9), enter the formulas
    =(INDEX(y1vals,J8+1)-INDEX(y1vals,J8))/(INDEX(x1vals,J8+1)-INDEX(x1vals,J8))*(commonXVal-INDEX(x1vals,J8))+INDEX(y1vals,J8)

    and
    =(INDEX(y2vals,K8+1)-INDEX(y2vals,K8))/(INDEX(x2vals,K8+1)-INDEX(x2vals,K8))*(commonXVal-INDEX(x2vals,K8))+INDEX(y2vals,K8)

    If you didn't use J8 and K8 as in the example above, you will have to adjust the J8 and K8 references in the big messy formulas to refer to the cells you did use.

    In K10, enter the formula
    =J9-K9
    Again, if you didn't use J9 and K9 for the formulas as in the example above, you will have to adjust the formula accordingly.

    Now, run Goal Seek (though, personally, I prefer the more sophisticated Solver) by selecting Tools | Goal Seek... Set the 'Set Cell' to K10, the 'To Value' to 0 (that's zero), and the 'By changing cell' to CommonXVal.

    Click OK and you should have the exact result.


  5. #5
    MrExcel MVP tusharm's Avatar
    Join Date
    May 2002
    Posts
    11,007
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default

    The worksheet, courtesy HTMLMaker:

    ******** ******************** ************************************************************************>
    Microsoft Excel - test_linear_interp-lana.xls___Running: xl2002 XP : OS = Windows 98
    (F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)bout
    =

    B
    C
    D
    E
    F
    G
    H
    I
    J
    K
    2
    x1 valuesy1 values x2 valuesy2 values x12.50632
    3
    -28.345-9.7821 -18.85922.095286
    4
    -17.89-5.6667 -9.26249-5.5094
    5
    0.92322.891 10.489764.272487
    6
    1.2122.9978 11.323879.759326
    7
    4.55213.67 11.4470323.39714
    8
    10.7525.003 15.0811331.4088 65
    9
    30.833.33 39.9089543.46393 25.7324225.73242
    10
    0
    11
    Names in sheet
    12
    commonXVal=Sheet1!$K$2
    13
    x1vals=Sheet1!$B$3:$B$9
    14
    x2vals=Sheet1!$G$3:$G$9
    15
    y1vals=Sheet1!$C$3:$C$9
    16
    y2vals=Sheet1!$H$3:$H$9
    Sheet1

    [HtmlMaker 2.20] To see the formula in the cells just click on the cells hyperlink or click the Name box
    PLEASE DO NOT QUOTE THIS TABLE IMAGE ON SAME PAGE! OTHEWISE, ERROR OF JavaScript OCCUR.


  6. #6
    Board Regular
    Join Date
    Mar 2011
    Location
    USA
    Posts
    84
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Intercept of two lines in excel chart

      
    I realise this post is several years old, but it is just what I need.

    Is there any chance somebody could explain what the formulas above actually do? I am looking to do the same thing, but end up with the Y value of the intercept, not the X.

    Thanks

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

 

 
DMCA.com