difference between 2 lines in a scatter chart

[prd02003]

I have created a scatter chart with two lines. The x axis is divided up into 10 parts and labeled 10, 20, 30, 40, etc. What I want to do is calculate the distance between the two lines between the x values of 40 and 60. Does anyone know how to to that? Thank you in advance.

[bj]

How did you generate the lines?
Are they from the data directly ot trendlines?

When you say you want to calculate the difference between x =40 to X=60,
Do you want the average difference?
do you want the difference at 40, at 60?

"prd02003" wrote:

>
> I have created a scatter chart with two lines. The x axis is divided up
> into 10 parts and labeled 10, 20, 30, 40, etc. What I want to do is
> calculate the distance between the two lines between the x values of 40
> and 60. Does anyone know how to to that? Thank you in advance.
>
>
> --
> prd02003
> ------------------------------------------------------------------------
> prd02003's Profile: http://www.excelforum.com/member.php...o&userid=25762
> View this thread: http://www.excelforum.com/showthread...hreadid=391727
>
>

[prd02003]

The lines are those generated by the smooth line effect in the scatter chart options in excel. They are not trendlines. Basically I want to measure the area between the two lines between the x values of 40 and 60. I have a bunch of really similar graphs and I want to see which graph has the two lines that are the closest together between the x values of 40 and 60. Does that help?

[MrShorty]

The chart itself is not going to readily be able to do this. The "smooth curve" is just a spline, and may or may not represent reality. I'm also not aware of any way to extract interpolated values from Excel's spline. Best bet, IMO, will be to obtain the area between the curves in the spreadsheet from which the data were plotted. Do you have data points between 40 and 60? What kind of curve (linear, exponential, etc.) do you expect?

[prd02003]

I do have data points for one of the lines between 40 and 60 but not for the other. The line I don't have a data points for is linear (intercept = 0, slope=1, data points 0 and 100) it represents an "ideal" and I am measureing how close the other data fits to the ideal. Moreover, the data is not linear in whole, but could be considered linear between 40 and 60. Does that help?

The chart itself is not going to readily be able to do this. The "smooth curve" is just a spline, and may or may not represent reality. I'm also not aware of any way to extract interpolated values from Excel's spline. Best bet, IMO, will be to obtain the area between the curves in the spreadsheet from which the data were plotted. Do you have data points between 40 and 60? What kind of curve (linear, exponential, etc.) do you expect?

[MrShorty]

So your reference line is defined as y=x (technically, then, you do have data points for this line in the 40 to 60 range, you just haven't used them to plot the line in the chart).

If we can assume that the curve of interest is linear over the region of interest (x=40 to 60), then the problem reduces very quickly to finding the area of a trapezoid, [l1+l2]*h/2. l1=f(40)-40, L2=f(60)-60, h=x2-x1=60-40=20.