# 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. 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. 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. I am using the chart wizard. Yes, i do have the data points that are connected into one line... any ideas?

4. 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. 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
 J8K8J9K9K10 =

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. ## 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

#### Posting Permissions

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