How To Use The Trapezoidal Rule In Excel?

This is what it is and how to use the trapezoidal rule in Excel.

What Is The Trapezoidal Rule?

We use the trapezoidal rule to estimate the integral of a function; that is to estimate the value of intervals of one or more variables.

The rule works by evaluating the area under a curve after first dividing the total area into smaller trapezoids. From there, we can calculate the area.

How to calculate the Trapezoidal Rule?

The steps below will take you through the process to create a table to chart a curve. A quick reference guide is available at the end of this document.

Open the spreadsheet How To Use The Trapezoidal Rule In Excel - Sample Data, go to Sheet 1

Create a table containing the variables and their range. We will use the values 0 – 6 as our limits of integration.

Determine for each interval how many subintervals you want. We will use 2 subintervals per interval or 1/2 =.5. The more slices used increases the accuracy of the result.

Using the subintervals identified enter the range of values in the column with the heading 'x'. Example list the values 1 through x, x being the upper value of the range you want to analyze.

Make the following entries:

integrals column heading

  • B2 - x, this is the column heading
  • B31
  • B4 - 5

Select both B3 and B4, left click the fill handle then drag the handle down to B13. This covers the range we identified.

drag handle down

Label the next column. In C2, type the column heading f(x)=1/x; the formula for this column.

Divide 1 by each of the intervals. In C3 type =1/B3, press enter.

divide one by interval

Use the fill handle to copy this formula to the cells of this column.

intervals divided by one

Label the next column. In D2 type the heading Area for each interval.

Under the heading skip the first cell, in the next cell (D4).

area of each interval

Type this formula =(0.5)*(C3+C4)*0.5.

area of intervals calculated

Use the fill handle to copy to the rest of this column.

copy the rest of column

Total the values in the Area for each interval.

sum formula intervals

In cell D14 type =SUM(D3:D13), press enter.

function by sum formula calculated for intervals

Quick reference guide

  1. We will use specific cell addresses in this guide to make the steps easier to follow.
  2. Open a new spreadsheet, add the following column headings: -
    • B3 - x
    • C3 - f(x)=1/x
    • D3 - Area for each interval.
  3. Determine the limits of integration, for example, 1 - 6
  4. Determine for each interval how many subintervals you want. For example, if you want 2 subintervals, Divide 1 by 2 =.5.
  5. Under column ‘x’, List the integration range in the increments you decided. In this example 1, 1.5, 2, 2.5, 3, 3.5, 4, 4.5, 5, 5.5, 6.
  6. Divide 1 by each of the intervals. In C4 type =1/B3, press enter.  Use the fill handle to copy this formula to the cells of this column.
  7. Area for each interval - skip the first cell in this column, in the next cell (D5), type this formula =(0.5)*(C4+C5)*0.5. Copy the formula to the remaining cells in this column.
  8. Total the values in the Area for each interval In cell D15 type =SUM(D4:D14), press enter.