Area between weird curves made out of lines (math problem?)

**dejhantulip** · 04-04-2012, 09:47 AM

Hello everyone!

This time I am in great need of a solution to my (kind of) math problem. In the attached file you will find a blue curve (made out of various small lines) and a red "curve" (made out as well of various small lines). The red line especially is not smooth at all, and in the future after the experiments I am running could get even less smooth.

Basically I am trying to find the area between the curves (or sets of lines--whichever you wanna call it). By iterating the C20 and C21 cells I am trying to obtain a similar area between the curves above the blue line and below the blue line. At the percentages shown in C20 and C21 I have come close to obtain "visually" a similar area. But I am lacking a more scientifically way of calculation the areas (above and below).

I have tried obtaining a 6th-order equation for the blue line, but if the blue line changes or becomes less smooth (again, after the experiments) I might now be able to have a realistic equation that represents the curve. The same goes for the red curve.

Please help me out, I am going crazy trying to solve the problem.

Thank you guys!

ORLANDO

**MrShorty** · 04-04-2012, 01:23 PM

The easiest way I see to do it is to assume each segment of each curve is a straight line. Thus, each segment will form a trapezoid relative to the x axis, and the area of a trapezoid is easy to find. Then the area between the curves over each segment is the difference between these areas. Then sum up over all segments. Then you can use Solver or Goal Seek to find the value in C20 which makes the area between the curves 0.

**dejhantulip** · 04-04-2012, 04:15 PM

Nice idea!

However, I think the fact that the x-axis is in log scale (the plot is a semi-log plot) would not make that idea work? In other words, would it be a problem if the "base" of the trapezoid is in log scale and the "heights" of the trapezoid sides are in "normal" scale?

Also, and most importantly, how could I differentiate how much area is ABOVE the blue line and how much is BELOW the blue line? Remember that I am not trying only to find how much area is between the curves, but actually how area is below and above.

Any ideas?

Thanks!

ORLANDO

Originally Posted by MrShorty

The easiest way I see to do it is to assume each segment of each curve is a straight line. Thus, each segment will form a trapezoid relative to the x axis, and the area of a trapezoid is easy to find. Then the area between the curves over each segment is the difference between these areas. Then sum up over all segments. Then you can use Solver or Goal Seek to find the value in C20 which makes the area between the curves 0.

**MrShorty** · 04-04-2012, 06:01 PM

I think the fact that the x-axis is in log scale (the plot is a semi-log plot) would not make that idea work?

Depends on how you want to do the analysis. A semi-log plot exaggerates distances at low x, and compresses distances at high x. The 9 units between 1 and 10 appear larger than the 9 units between 10 and 19, but the distance is still 9 units.

If the analysis is supposed to be carried out assuming a linear scale, then what I've suggested should work just fine. If you want to do it on a log scale for x, then you'll need to add a column for the logarithm =log(x), then compute the areas using the log(x) column for the x values. I can't say which is more correct for your analysis, but it can be done either way.

Also, and most importantly, how could I differentiate how much area is ABOVE the blue line and how much is BELOW the blue line?

When you take the difference of the two areas, some results will be negative and some will be positive. If you've taken the difference as Red-Blue, then a positive number indicates that red is above blue, and a negative result indicates that blue is above red.

look at the additions I made to your spreadsheet to see if they make sense to you.