line intersection (this is a hard one.)

Dhussar · Mar 6th, 2002, 08:59 AM

I have two lines, one is a straight line which I can easily develope an equation for, the other is an irregular curved line that I only have data points for. How do I automatically determine the value where they meet?

Damon Ostrander · Mar 6th, 2002, 10:05 AM

Hi Dhussar,

There are many ways to do this. Which way depends on your problem and needs. First, how accurate does the solution have to be? How close together and how "curved" is the curve. If close together or not very curved then very simple linear interpolation between points would probably be appropriate. If the curve is represented by sparse points or has a lot of curvature then the best way would probably be to curve fit and equation, probably a polynomial power series, to the points in the vicinity of the intersection, and solve the two equations simultaneously to find the intersection.

Another factor that must be considered is whether the data of the curve is "noisy", e.g., did it come from measurements that contain measurement error? If so a least-squares curve fitting method is appropriate since it allows the errors to exist but minimizes their effects.

Perhaps if you could describe your problem in a bit more detail, I could make more specific recommendations, and even propose a specific algorithm or code.

Dhussar · Mar 7th, 2002, 09:46 AM

I sent a private message to you but not sure if you got it. Anyway, I'm trying to find the yield point of a tensile test. The graph starts flat, increases rapidly in a linear fasion, flatnes out with a slight increase, and then drops back to zero. The yield point is defined as the point where a straight line offset from this linear portion by .2% crosses the point where the graph flatens. I don't know if this is much help, its a lot to say in the limited space. The data looks something like this:
y x
1 .01
2 .02
2 .03
5 .04
10 .05
15 .06
20 .07
25 .08
26 .09
27 .1
28 .11
4 .12
2 .13
1 .14
0 .15

Mar 6th, 2002, 10:05 AM	#2
Damon Ostrander MrExcel MVP Join Date: Feb 2002 Location: Denver, Colorado USA Posts: 4,014	Hi Dhussar, There are many ways to do this. Which way depends on your problem and needs. First, how accurate does the solution have to be? How close together and how "curved" is the curve. If close together or not very curved then very simple linear interpolation between points would probably be appropriate. If the curve is represented by sparse points or has a lot of curvature then the best way would probably be to curve fit and equation, probably a polynomial power series, to the points in the vicinity of the intersection, and solve the two equations simultaneously to find the intersection. Another factor that must be considered is whether the data of the curve is "noisy", e.g., did it come from measurements that contain measurement error? If so a least-squares curve fitting method is appropriate since it allows the errors to exist but minimizes their effects. Perhaps if you could describe your problem in a bit more detail, I could make more specific recommendations, and even propose a specific algorithm or code. __________________ Keep Excelling. Damon VBAexpert Excel Consulting (My other life: http://damonostrander.com )

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Mar 6th, 2002, 08:59 AM	#1
Dhussar New Member Join Date: Mar 2002 Posts: 5	I have two lines, one is a straight line which I can easily develope an equation for, the other is an irregular curved line that I only have data points for. How do I automatically determine the value where they meet?

Mar 7th, 2002, 09:46 AM	#3
Dhussar New Member Join Date: Mar 2002 Posts: 5	I sent a private message to you but not sure if you got it. Anyway, I'm trying to find the yield point of a tensile test. The graph starts flat, increases rapidly in a linear fasion, flatnes out with a slight increase, and then drops back to zero. The yield point is defined as the point where a straight line offset from this linear portion by .2% crosses the point where the graph flatens. I don't know if this is much help, its a lot to say in the limited space. The data looks something like this: y x 1 .01 2 .02 2 .03 5 .04 10 .05 15 .06 20 .07 25 .08 26 .09 27 .1 28 .11 4 .12 2 .13 1 .14 0 .15