difference between 2 lines in a scatter chart
 

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

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

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?


--
prd02003
------------------------------------------------------------------------
prd02003's Profile: http://www.excelforum.com/member.php...o&userid=25762
View this thread: http://www.excelforum.com/showthread...hreadid=391727

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
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=391727

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?







MrShorty Wrote:
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
------------------------------------------------------------------------
prd02003's Profile: http://www.excelforum.com/member.php...o&userid=25762
View this thread: http://www.excelforum.com/showthread...hreadid=391727

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.


--
MrShorty
------------------------------------------------------------------------
MrShorty's Profile: http://www.excelforum.com/member.php...o&userid=22181
View this thread: http://www.excelforum.com/showthread...hreadid=391727