Finding range of linear portion of data

**oucivileng** · 12-12-2010, 08:49 PM

I need to define a best fit line for the linear portion of a set of data so that I can find where this line intersects with another line that I've already defined. This would be easy to do by plotting and using a trendline, however I would have to do this a few thousand times. If I can figure out a way for excel to at least define a range where the data is fairly linear, I can put a line through it. Any ideas? Maybe with a maximum standard deviation? I've attached a picture of a plot of data similar to what I'm using. The line with the negative slope is what I need to define.

Thanks!

**shg** · 12-13-2010, 01:38 AM

You could find the minimum for the horizontal line, and the two points on either side having the maximum radius of curvature. Draw a line through each those points parallel to the pair of points on either side of it, and compute your intersections.

**oucivileng** · 12-13-2010, 11:35 AM

I'm not sure I understand what you're saying. The intersections are not the problem, I just need a best-fit line through the linear part of the curve just to the left of the minimum. The range of this linear part changes somewhat with each test I run. If I could just figure out a way for excel to find this range for me, I could use the first derivative of the section to find the slope. I thought maybe I could use a string of IF statements to find a range where the standard deviation of the first derivative is below a value that I decide on, but I feel like it would have to be an iterative process and I'm not real keen on how to do that in Excel. Any thoughts? Anyone?

**shg** · 12-15-2010, 07:33 PM

How linear is "linear"? How noisy is the data?

The radius of curvature measures how curved the function is. You could calculate that at every point, and then decide what the "linear" portions of the curve are, and then regress a line through those portions.

It's hard to make a suggestion for a picture.