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.

 

[RalphA]

If you are talking about straight lines, then, let Excel calculate the formulas of each of the two lines, which will be of the form,

y1 = m1x + b1 for curve 1, and
y2 = m2x + b2 for curve 2.

If you want the distance of the two curves at the three points where x = 40, 50, and 60, then, say:

In cells B1, B2, B3, and B4, enter, respectively, "x", 40, 50 and 60.

In cell C1 enter "Distance"
In cell C2, enter =(m1*B2 + b1) - (m2*B2 + b2), and copy down to C3 and C4.

 

[prd02003]

thanks for the reply, but I want to clarify, I want to measure the area between the two curves between the x values of 40 and 60. Does anyone know how to do that?

 

[RalphA]

If you caclulate the distances between lines at x = 40 (call that Dy40) and at x = 60 (call that Dy60), using the description in my previous post, the area can be calculated, for a general case, as:

Area = (x2 - x1)*(Dy1 + Dy1)/2 and, for the present case of x2 = 60 and x1 = 40:

Area = (60 - 40)*(Dy40 + Dy60)/2

 

[prd02003]

Thanks for the reply but I dont think that your formula will work. I think your formula assumes that the distance between the two lines is constantly increasing. In fact, my lines zig and zag pretty sporadically. Someone told me today it has something to do with calculus and maybe an integral (I never had calculus). Could this be right? If so, where would I go to get that mathematical formula? Any math majors out there?

 

[RalphA]

If you read my posts again, you will understand that, since you only talked about "lines", I assumed you were talking about a straight line. You have not posted, until now, any information that one could assume you were not talking about straight lines. Your posts seem to me to represent moving targets. I now understand that you are talking about two curves!

I feel that the only way you are going to get the correct reply to your qustion is for you to post the actual data.

 

[Jon Peltier]

In another forum, this question was asked, and the OP (presumably our OP here) wanted the area between the bezier lines drawn when the Smoothed Line checkbox in the Format Series - Patterns dialog is checked. I uncharacteristically kept my mouth shut, all the while thinking those **** bexier curves are no better than what the real data would be, and even using a trendline to smooth over intermediate points would probably be the most appropriate way to determine the lines.

 

[Oaktree]

If your data "zigs and zags", it sounds like it's a series of straight lines that connect your data points. You can probably figure out the area by adding the area of simple geometric shapes.

Still though, as Ralph suggested, you'll get better answers if you post the actual data than if we speculate as to what you *might* be graphing.