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]

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. Th
"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 fro
Excel's spline. Best bet, IMO, will be to obtain the area between th
curves in the spreadsheet from which the data were plotted. Do yo
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 fo
the other. The line I don't have a data points for is linear (intercep
= 0, slope=1, data points 0 and 100) it represents an "ideal" and I a
measureing how close the other data fits to the ideal. Moreover, th
data is not linear in whole, but could be considered linear between 4
and 60. Does that help?

 

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