Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
#6
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Missing lines in chart w/x-axis with months 1-24... | Charts and Charting in Excel | |||
Leader Lines Connecting Chart to Box | Excel Discussion (Misc queries) | |||
Leader Lines Connecting Chart to Box | Charts and Charting in Excel | |||
Pie chart with 'anchored' lines to text box | Charts and Charting in Excel | |||
Scatter Chart Axis as text in C# Web appl | Charts and Charting in Excel |