Results 1 to 6 of 6

Intercept of two lines in excel chart

This is a discussion on Intercept of two lines in excel chart within the Excel Questions forums, part of the Question Forums category; Does anybody know how to find the exact value of the intercept of two lines in excel chart. I can ...

  1. #1
    New Member
    Join Date
    Aug 2002
    Location
    USA
    Posts
    2

    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
    10,922

    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

    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
    10,922

    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
    10,922

    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
    77

    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

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