How to calculate area between two lines in scatter chart

Hi folks,

I have created a scatter chart in excel and using two lines makes up a shape (trapiziod).

I would like to know if there is a manner in which the area can be determined between these lines and if so what do I do?

Regards
ROb

You would need to use formula to calculate the area.
And if you have managed to plot the shape via a scatter chart you should have all the data points needed to calculate the area.

Google returns plenty of examples of area of trapiziod, and distance of lines.

Hi Andy,

Thanks for your response.

The shape is generally trapezoidal in nature however it is not as simple. That is why I am trying to get some understanding of how to do the calculations between the two lines.

The upper line is trapezoidal where the lower line is determined by ground elevations and thus impacts the area inside the shape in various ways.

I hope this provides better clarity in terms of my endevours

Not really.

Can you post an example work book of what you have?

The shape is generally trapezoidal in nature however it is not as simple.

That may be true overall, but each small slice is probably reasonably approximated by a trapezoid -- which leads to the idea of a Riemann sum. For something as "unpredictable" as ground elevation/topography, I would expect to use a numerical integration/Riemann sum for such a task.
https://en.wikipedia.org/wiki/Riemann_sum

I hope my attachment comes through ok.

The blue arrow indicates the section I am wishing to calculate. It is a cross section through a dam wall. The main XY coordinates are the ariel view and position of the section and the section is the elevation of these points.

The lower line is the natural ground line and in other sections I have created a seprate line for this in a scattered chart.

I hope this information provides more clarity.

Thanks MR Shorty for your inputs.

I do not know Rieman methods but have tried simpsons rule.

The idea is to sum a total of nearly 3500 sections and use the end areas rule or simpsons rule to calcultae a volmue using the areas show in the worksheet.

I have a long way to go so thanks agian for your help

Since Simpson's rule is more complicated than the simple Riemann sums described in the Wikipedia article, if you know how to do Simpson's method, then it should not be overly difficult to do any of the described Riemann sums. I typically do both left-hand and right-hand sums and the average of those (the trapezoidal rule). Then look at those three results and see what they tell me about the area/integral.

Do you have specific questions about implementing any of these sums in a spreadsheet?

MRShorty - Thanks for your replies.

In my spreadsheet I have three cross sections. The green line is the design of the shape. The red what will be the actual completed profile. The dotted line is the natural ground line.

The idea is to measure the area between the dotted line and the red line as the project progresses and from these areas I can determine the volumes of work done.

My apologies for the simplicity in explanation.

I have tried various methods with no level of confidence in the result. In answering your question I am then not sure if my calculations are incorrect.

I will be moving forward once I have these calculation resolved to try and shade then the area between the two lines so that I can visually represent the section showing the work done, hence the development of the third section in the worksheet

For some reason, when we were talking about elevation, I was thinking large scale (mountains, valleys, canyons, etc.), so I was expecting something rather more complicated than this scenario. This looks much simpler.

Looking at just the red/yellow chart, it looks to me like the area between the red and yellow lines can be easily represented by 4 simple shapes. From left to right:
1) A triangle formed by the first two red points and a drop line down to the yellow line. In this case, this is a simple right triangle, so area of this triangle is b*h/2.
2) A trapezoid between the 2nd red point and the centre line. Area of a trapezoid is (b1+b2)*h/2. You will need to interpolate between red2 and red3 to find the height of the red line at the centre line.
3) Another trapezoid between the centre line and the red3 point. you will need to interpolate between yellow2 and yellow3 to get the intersection point for red3 drop line.
4) A final triangle between red3, red4, and the sloped yellow line. I recognize that, because yellow3 and red4 are not the exact same point, there is some question of exactly how to handle this section, but this particular example, they are really close.

My main question is how flexible does the programming need to be. Is this a one time thing, or is this something you do frequently? Is this an accurate representation, or can the relative shapes of red and yellow be very different from this? If this is a one time thing, or the basic profile is always the same (triangle -> trapezoid -> trapezoid -> triangle), then I would probably just quickly cobble something together to separate calculate the area of the four shapes. If this is a frequent task and the red and yellow lines can represent a wide variety of profiles, I would probably be more careful in my programming so I end up with something that can handle a wider variety of problems (probably handling each "section" as a trapezoid -> remembering that a triangle is a trapezoid where one base has length 0).

Decomposing the overall irregular shape into trapezoids (and triangles and rectangles where triangles and rectangles are special cases of trapezoids) is how I envision solving a problem like this. What help do you need in setting that up in the spreadsheet?

HI MRShorty - Thanks for your reply.

In my reply:
1. I agree and I have tried that - it is the 3rd triangle to the right of the section that provides a challenge in that.
2.Correct
3.Correct
4. It is mentioned as being the "problem child"

"How flexible . . . " - The dotted line values will eventually change and probaly have more points included to it giving a more realistic impression of what the natural ground line looks like, and the method of using triangles become more complicated. The design shape, giving the shape of the trapezoid (4 points does not change. Teh red trapezoidal line is representative of the work being done (rate of rise of the wall) and the difference between that libne and the dotted NGL will be the work done.

This area needs to be shaded and then looking at a combined chart one can see exactly what is happening as time goes by.

The other thought is that I interpolate points on the NGL as well as the design shape and actual shapes.These interpolated points should align in the vertical plane and distances calculated between them. Then using a formula like simpsons rule or reihmann calculate the area. The method of calculation needs to be the same for each section and this may bring some inaccuracy to the final volume calculated. The method needs to be simple.

WE have similar thinking and the simplicity of calculationg using a simple combination of triangles , will provide some confidence to the end result, in terms of the areas calculated.

The exercise is not to calculate volumes of mountains, it is to calculate the volumes of material in a tailngs dam, as it is constructed. The length of the wall is nearly 4 kilometers long and in some places nearly 20m high.

I would appreciate if you could help with the spreadsheet.

Regards
Rob

4. It is mentioned as being the "problem child"

I can see a few different ways to deal with this "problem child", but obviously cannot know anything about your requirements, so I cannot know what kind of approach you want to use for this segment. Even if you are uncertain how to program the desired geometry, we will probably need you to tell us how you want to handle the problem encountered when the yellow and red curves do not terminate at the exact same point.

The other thought is that I interpolate points on the NGL as well as the design shape and actual shapes.These interpolated points should align in the vertical plane and distances calculated between them. Then using a formula like simpsons rule or reihmann calculate the area. The method of calculation needs to be the same for each section and this may bring some inaccuracy to the final volume calculated.

This is basically the approach I have in mind (subdivide the area into trapezoids which requires interpolation between points, find the area of each trapezoid, then sum up the areas to get the final result), but you seem hesitant to use this. In post #9, you say that you have tried various methods with no confidence in the results. Before we get involved in the programming details, I would want to know if you have already tried and dismissed this, and/or if you have an approach you would prefer to use, and/or some of what you have tried. I would not want to spend the time programming an algorithm in that you have already dismissed as not applicable to this particular problem.

Hi MR Shorty,

My apologies in delay. It is a bit hectic this end.

I have progressed somewhat in calculating areas. I have used the co-ordinate method, all be it a bit cumbersome. I set up a square 4x4 and used to method so as to get confidence that the result using this approach would be correct.

So moving on I have created the section using co-ordinates and areas are thus being calculated. I need to consider the formula for determining the volume. Perhaps Simpsons rule.

Your assistance in inteploation between two points would be of great help as I do not find anything that is simple to use as yet.
Then I need to find a way to shade between two lines and lastly,
I need to think of a simple way to generate all the other sections so that the volume calculations will be easier.

I am having trouble reverse engineering your spreadsheet to see where you are calculating areas of each part. It looks like you are calculating areas as rectangles rather than trapezoids.

Unlike your previous example, this one does not need any interpolation. All of the points for all of the profiles use the same X coordinate. In this case, I would calculate the area of each section as a trapezoid [area=(base1+base2)*height/2] defined by the four points of each section. The vertical distances are the bases and the horizontal distance is the height. Assuming we are still working with the red (actual) and the yellow (topsoil) curves:

1) In O61, enter the area of the first section =(O10-O22+P10-P22)*(P21-O21)/2. Copy/paste/fill across to AA61.
2) In M61, enter =SUM(O61:AA61). This should be the area between the red actual curve and the yellow topsoil curve. If you want the area between different curves, simply use the correct references in the O61:AA61 formula.

At this point, you should have the area of the shape as a sum of the individual trapezoids that make up the shape. Does that seem like an acceptable algorithm for calculating the area?

Of course, area is a 2D concept, but you also talk about volume. Mathematically, the only difference between area and volume is that volume is a 3D concept where area is a 2D concept. Conceptually, computing area in 2D is the same as calculating volume in 3D -- break the shape up into smaller "blocks" that you can readily calculate the volume of, then add up those volumes. I don't see anything in your spreadsheet that tells me anything about that third "depth" dimension, so I am not sure how you intend to go from area to volume.

How representative is this of your actual project? In this example, we did not need an interpolation algorithm, but we would have needed to interpolate in your previous example. Which example is more representative of your real world needs? If you decide that you will need an interpolation algorithm, the usual approach I recommend in Excel is described here: https://www.excelforum.com/excel-for...ml#post5262186