How to Use the Trapezoidal Rule in Microsoft Excel

By Sean Hill; Updated April 25, 2017

The trapezoidal rule is used to approximate the integral of a function. The rule involves treating the area under a curve as a series of trapezoidal slices. Implementing this rule in Excel requires inputting a curve's independent and dependent values, setting integration limits, setting slice parameters and using a function to determine area.

Input the curve you wish to analyze in an Excel spreadsheet. Put the independent values (i.e., x values) in the first column. Put the dependent values (i.e., f[x] values) in the second column.

Determine the desired limits of integration. For example, if you want to find the area under a curve between x = 0 and x = 5, your limits of integration are 0 and 5.

Delete any values outside the limits of integration in the first two columns of your table.

Determine the desired number of trapezoidal slices. Divide this value by the range of your integration limits to get slice length. For example, if you want five slices between x = 0 and x = 5, your slice length will be one.

Delete all independent values that are not either an integration limit or a multiple of the slice length. Delete all corresponding dependent values.

Create a function in the top box of the third column: one-half the slice length times the sum of f[x] and f[x+1]. If your dependent values start in the first row and second column, type (1/2)(slice length)(B1+B2).

Drag the bottom right of this function box down until the third column is one value short of the first two columns.

Sum the values of the third column to get the approximate value of your integral.

Tip

As an example of what you should delete, if you erase x = 2.5, you should also erase the corresponding f[x = 2.5].

When you delete anything from the first or second columns of your spreadsheet, move the remaining values together so the only value with empty boxes under it is the last one.

To start creating a function in Excel, click on a box and press the "=" key. Press "Enter" when you're finished typing the function.

If your dependent values start on a different row or column, use those alpha-numeric parameters for your trapezoidal function. For example, if your values start in the third row and third column, use C3 and C4 for initial parameters.

When you drag the function box down, the other boxes will fill in automatically. If the other boxes display an error message, you have typed the function incorrectly.

To sum the third column's values, click on any empty box, type "= SUM(", highlight the third column, type ")" and press "Enter."

References

About the Author

Sean Hill has been writing professionally since 2006. He writes about legal and engineering topics for eHow and Answerbag. He is pursuing a Juris Doctor at the University of Texas, and he holds a Bachelor of Engineering in biomedical engineering from Vanderbilt University.