Introduction

Sometimes we want to measure a surface, e.g. of a room or a piece of land.  This is easy if the shape is simple, e.g. a single rectangle, or if it is made from a few rectangles.

It becomes more difficult if the shape is rather complex.  In the drawing below, the rectangle is easy:  its surface is length times width (a×b).  The L-shape is also easy: if we have the lengths of all the sides it can be calculated as the sum of two rectangles (a×b+c×d).  The third shape could still be decomposed into rectangles and triangles, but it becomes tedious.

A major advantage for using a spreadsheet is the ease with which errors can be corrected.

We are going to make a spreadsheet formulae to find the surface of an arbitrary polygon from the location of its vertices.

Coordinates

We will define the polygon by its points (vertices) in a Cartesian coordinate system.

For the third shape this could be something like:

Where point A is at 1 horizontally (x-coordinate) and 4 vertically (y coordinate).  B is at 3 along x and 5 along y, and so on.  We make a table containing all that information:

Computing Areas

Any Shape

Look at any shape that has been drawn in a Cartesian coordinate system, say the grey “potato” in the drawings below:

Its area is equal to the area “under” the top part of its curve (between the top part and the x-axis; light blue) minus the area “under” the bottom part (light yellow).

The area under a curve turns out to be quite easy to calculate.

Line Segments

As we are only dealing with polygons, our curves are composed of straight line segments.  Let us look at a single line segment PQ, as in the drawing below, and consider the area “under” it, i.e. between the line PQ and the x-axis.

This is the area SPQR of a trapezium (trapezoid), and that area is SR×(SP+RQ)/2.  Indeed, it is nothing else than the average height (SP+RQ)/2 times the width SR (this is therefore also the area of the blue dotted rectangle).

But if we have the coordinates x_P,y_P of P and x_Q,y_Q of Q, then SR = x_Q-x_P, while SP is simply y_P and RQ is simply y_Q.

Thus the area is (x_Q-x_P)×(y_P+y_Q)/2

Polygons

In the next drawing we consider the polygon ABCD:

The area of ABCD is that of A’ABCC’A’ (under the top “curve”) minus that of A’ADCC’A’ (under the bottom “curve”).

If we consistently “walk around” the polygon visiting its points A, B, C, D and back to A, in that order, then the formula for the area under a segment will automatically be positive when we go from left to right because the value of, say, (x_B-x_A) will be positive, and become negative when we go from right to left, because then the value of, say, (x_D-x_C) will be negative.

We are now ready to put this in a few spreadsheet formulae.

Spreadsheet

Take the table again.  I have labelled its rows and columns like in a spreadsheet.  We add a column at the right, in which we put the formula (x_Q-x_P)×(y_P+y_Q)/2 for the area under a line segment, and two extra rows at the bottom:

Obviously it is sufficient to write the formula once, in cell D3, then propagate it down the column.

We need to “close” the polygon by linking up point J back to point A.  That is done in the first added row (row 12).  Finally we need to make the sum of all the areas, e.g. in cell D13.

In a real spreadsheet the table would display like below (the figure is repeated to its right for clarity).

The area is 10.5.  In the area column it is apparent that vertical line segments have an area 0 (zero) under them, and segments going from right to left have negative areas.

It is important to add the extra segment that goes from point J back to A, otherwise the area under it will not be counted.

It is also important to put the coordinates of the points in the order they occur when one goes around the shape.