Determine intersection point of two lines on chart

[doublej41]

Hi All,

I have a chart set up that plots 3 data series'. One is some user input data and the other two are upper and lower tolerances.

Is there an easy way to get the y-axis value at the points where the user data intersects either of the tolerances? They may intersect more than once so I would need all points to be able to determine the maximum y-axis value.

I would preferably like to stay away from VBA.

Thanks

 

[Gerald Higgins]

Can you give us some more detail on what your data looks like ?

For example, if your data looks something like this . . .

Upper..........Lower........User
Tolerance....Tolerance...Data
1................5..............3
1................5..............4.5
1................5..............6

and so on...

then it is easy to highlight instances of user data that are outside tolerance.

If, on the other hand, your tolerance levels are more complicated, then the task will be more complicated.

 

[doublej41]

Thanks for your reply Gerald.

The tolerances are calculated by an equation and are curves, rather than straight lines. Both the x and y values increase with each point until the maximum value.

The user data is captured with some software. The values start at the maximum and gradually decrease.

Here is some example data:

Excel Workbook
	B	C	D	E	F	G	H	I
1	User Data (x)	User Data (y)	*	Upper Tol (x)	Upper Tol (y)	*	Lower Tol (x)	Lower Tol (y)
2	10	271,417	*	0,15	0	*	0,15	0
3	10	271,417	*	0,163538673	0,06439214	*	0,163538673	0,019297874
4	9	181,932	*	0,1770672	0,123036766	*	0,1770672	0,044474022
5	9	180,527	*	0,190585591	0,180097791	*	0,190585591	0,071364839
6	8	124,779	*	0,204093859	0,236251372	*	0,204093859	0,099294475
7	8	125,154	*	0,217592015	0,291793571	*	0,217592015	0,127967176
8	7	89,965	*	0,23108007	0,346889342	*	0,23108007	0,157218297
9	7	88,925	*	0,244558035	0,401643123	*	0,244558035	0,186943713
10	6	66,905	*	0,258025922	0,456126595	*	0,258025922	0,217072051
11	6	66,523	*	0,271483742	0,510391805	*	0,271483742	0,247551581
12	5	50,513	*	0,284931507	0,564478137	*	0,284931507	0,278343229
13	5	50,443	*	0,298369227	0,618416362	*	0,298369227	0,309416539
14	4	37,255	*	0,311796914	0,672231139	*	0,311796914	0,340747169
15	4	37,255	*	0,325214579	0,725942642	*	0,325214579	0,37231526
16	3	25,4	*	0,338622233	0,77956766	*	0,338622233	0,404104341
17	3	25,559	*	0,352019888	0,833120374	*	0,352019888	0,436100549
18	2	15,221	*	0,365407555	0,886612909	*	0,365407555	0,46829208
19	2	15,224	*	0,378785244	0,940055743	*	0,378785244	0,500668774
Sheet1

 

[Gerald Higgins]

OK, I understand . . . partly.

Which of those User Data points are outside tolerance (if any) and why ?
I.e. how did you calculate that they are outside tolerance ?

 

[doublej41]

I have just plotted the data and realised it doesn't give a great interpretation of what I am looking for.

Here is an example of a type of chart I am talking about:



I want to know the y axis value of the points where the blue line cuts either of the red ones.

The data is in the form as given above, however that is only a small sample.

Does this make more sense? It is possible to attach files on here?

 

[wigi]

Does this make more sense? It is possible to attach files on here?

No, but please present a REPRESENTATIVE table with data.

 

[doublej41]

Sorry for the confusion.

Here is the data that produced the graph above:

Excel Workbook
	B	C	D	E	F	G	H	I
1	User (x)	User (y)	*	Upper Tol (x)	Upper Tol (y)	*	Lower Tol (x)	Lower Tol (y)
2	10	271,417	*	0	0	*	0	0
3	10	271,417	*	1,870748299	14,30489152	*	1,870748299	9,802356485
4	10	273,263	*	1,906779661	14,61967746	*	1,906779661	10,03546254
5	10	272,34	*	1,942567568	14,93428228	*	1,942567568	10,26874972
6	9	181,932	*	4,210875332	40,04777843	*	4,210875332	29,53450557
7	9	180,527	*	4,915048544	50,76595874	*	4,915048544	37,99254526
8	9	181,324	*	5,381548975	59,0007367	*	5,381548975	44,5508513
9	9	181,324	*	5,397727273	59,30524832	*	5,397727273	44,79423168
10	9	181,932	*	5,4138322	59,60972778	*	5,4138322	45,03764422
11	8	124,407	*	5,429864253	59,91417529	*	5,429864253	45,28108871
12	8	124,038	*	6,096311475	73,88728237	*	6,096311475	56,51101363
13	8	124,407	*	6,109406953	74,19041993	*	6,109406953	56,75576807
14	8	124,779	*	6,680633147	88,71434489	*	6,680633147	68,53065911
15	7	89,965	*	7,531796502	116,4315058	*	7,531796502	91,21956219
16	7	88,925	*	8,092383639	140,4343708	*	8,092383639	111,0480572
17	7	89,231	*	8,277027027	149,7153534	*	8,277027027	118,7517266
18	7	88,712	*	8,5592686	165,5602441	*	8,5592686	131,9451119
19	6	66,905	*	8,952894438	191,8133837	*	8,952894438	153,9064683
20	6	66,523	*	8,960928652	192,40932	*	8,960928652	154,406316
21	6	66,711	*	8,964932127	192,7072767	*	8,964932127	154,6562513
22	6	67,092	*	8,968926554	193,0052259	*	8,968926554	154,9061941
23	5	50,513	*	9,035476718	198,0692168	*	9,035476718	159,1563672
24	5	50,443	*	9,179064825	209,6787144	*	9,179064825	168,9146576
25	5	50,739	*	9,483590734	237,9160698	*	9,483590734	192,7270422
26	5	50,985	*	9,997998399	301,0447361	*	9,997998399	246,2993719
27	4	37,255	*	10	301,3406	*	10	246,5514
28	4	37,255	*	*	*	*	*	*
29	4	37,01	*	*	*	*	*	*
30	4	37,174	*	*	*	*	*	*
31	3	25,4	*	*	*	*	*	*
32	3	25,559	*	*	*	*	*	*
33	3	25,61	*	*	*	*	*	*
34	3	25,507	*	*	*	*	*	*
35	2	15,221	*	*	*	*	*	*
36	2	15,224	*	*	*	*	*	*
37	2	15,224	*	*	*	*	*	*
38	2	15,224	*	*	*	*	*	*
Sheet1

I would like to know when the user data intersects either of the tolerance curves.

 

[wigi]

Hello

Try Andy Pope's very nice code: http://www.andypope.info/charts/intersection.htm

 

[Gerald Higgins]

I can't see the chart you posted, but I tried plotting it myself and it LOOKS as if the user data never goes outside tolerance, although it seems to touch the upper tolerance line at one point.

If you know the equations that drive the tolerance lines, then presumably you can calculate the relevant values for any individual user data point, and highlight whether or not it is within tolerance.

 

[YamahaG22]

ComplexIntersection.xls
	W
13
Sheet1

Can anyone tell me why this application is not able to find the intersection points of the two plotted lines?