Microsoft Office Tutorials and References
In Depth Information
Calculating the area under a curve
If you don’t feel like counting squares, you can take a more calculated approach
and use formulas. This is known as the “trapezoid” method. A trapezoid, as you
may recall, is a four-sided figure with two parallel sides. This method essentially
divides the area under the curve into a series of trapezoids and then calculates the
area of each one. The area under the curve is the sum of the trapezoid areas.
The general formula to calculate the area of a trapezoid is to multiply the “aver-
age” height by the base. In the preceding example, the left side of the first trapezoid
has a height of 1 and the right side has a height of 2. The average height is 1.5. The
base is one unit, so the area of the first trapezoid is 1.5. The area of the second
trapezoid is 2, and so on.
The formulas in column C calculate the area for each trapezoid. Cell C2, for
example, contains the following formula:
This formula is copied down to accommodate the number of data points. Note
that the last cell (cell C7) is empty. That’s because each formula refers to the
subsequent row, and the formula is not valid for the last row of data. The formula in C9
simply adds these segment areas together.
This formula works fine — except when negative values are involved. In such a
case, the formula gets much more complex because triangles (as well as trapezoids)
enter the picture. The curve shown in Figure 8-47 presents more of a challenge
because it has negative values. Using the previous formula to calculate trapezoid
areas for this chart, the result is 3.5, which is clearly incorrect.
When negative numbers are involved, a more complex formula is required. The
formula below is a general-purpose formula that works in all situations.
The formula uses an IF function that determines whether the calculation returns
the area of a trapezoid, of the area of two triangles. In this example, the formula is
used four times to yield the final result. The first, second, and third calculations
compute the area of a trapezoid. The fourth calculation, however, computes the
area of the two triangles that result from the line crossing the x axis. The sum of
the areas of these two triangles is 0.83. The total area under the curve is 4.83.
It’s important to understand that the area calculation is approximate . Generally,
the accuracy of the calculation increases with the number of data points that define
the curve. Figure 8-48 shows three charts, all of which plot a sine curve. The charts
vary, however, in the number of data points used and, subsequently, in the number
of area calculations performed. The calculated area under the curve ranges from
220.01 to 229.16, the latter being the most accurate.