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:

=((B2+B3)/2)*(A3-A2)

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.

=IF(B2*B3>=0,ABS(((B2+B3)/2)*(A3-A2)),ABS(((B2^2+B3^2)/(B2-B3)/2)*(A3-A2)))

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.