Trapezoidal Integration is one of the 2 methods of Numerical Integration in Excel. In Excel, there is no built-in function to calculate the integrals. Basically, we will use the concept of finding the area under a curve to calculate the integration of a function by the Trapezoidal Integration method. In this article, we will learn 3 simple ways to **do** **Trapezoidal Integration in Excel**.

## Introduction to Integration in Excel

As there is no direct way to find the integral of a function in Excel, we will use the concept of **Numerical Integration**. **Numerical Integration** is basically a method that approximates the integral of a function by using some numerical approaches. In Excel, we can calculate integrals by following the **2** methods.

*Trapezoidal Method**Midpoint Rule*

## What Is Trapezoidal Integration?

**Trapezoidal Integration** evaluates the area under a curve to find the integral of a function within a definite limit. In this method, the area under a curve is divided into very **small trapezoids**. Later on, by summing all the small trapezoids under the curve we can calculate the total area under the curve, which is the integral value of the function.

## Trapezoidal Integration Formula

For instance, letâ€™s say we have a function f(x) with limits **p** and **q**.

**âˆ« ^{q}_{p }f(x) dx**

Now, to approximate the integral of the function f(x) by the **Trapezoidal Method**, we will use the following formula.

**âˆ« ^{q}_{p }f(x) dx=(q-p)*(f(p)+f(q))/2**

## How to Do Trapezoidal Integration in Excel: 3 Methods

In this section of the article, we will learn **3** simple methods to do **Trapezoidal Integration in Excel**. Let’s say, we have a **Data Chart** for **Distance (m)** covered for the exertion of **Force (N)**. So, the amount of **Work Done (J)** will be:

*Work Done = Distance Covered * Applied Force*

Now, we will use the **Trapezoidal Integration** approach to approximate the **Total Work Done (J)**.

Not to mention that we have used the *Microsoft Excel 365 *version for this article; you can use any other version according to your convenience.

### 1. Using Mathematical Formula

In the first method, we will use the mathematical formula to do the **Trapezoidal Integration in Excel**. Let’s follow the steps mentioned below to do this.

**Steps:**

- Firstly, use the following formula in cell
**D6**.

`=(B6-B5)*(C5+C6)/2`

Here, cell **B5** and cell **B6** refer to the **1st** and **2nd** cells of the **Distance (m)** column respectively. On the other hand, cells **C5** and **C6** indicate the **1st** and **2nd** cells of the **Force (N)** column.

- After that, press
**ENTER**.

*Note: **Here, each cell of the Work Done (J) per Interval column represents each area of the small trapezoids. After adding all the areas together, we will find the value of the integral.*

As a result, you will have the **Work Done (J)** for the **1st** interval.

- Then, drag the
**Fill Handle**up to cell**D20**to get the remaining outputs.

- Now, use the following formula in cell
**D21**.

`=SUM(D6:D20)`

Here, the range **D6:D20** indicates the cells of the **Work Done (J)** **per Interval** column, and the **SUM function** will return the summation of the values of range **D6:D20**.

- Following that, hit
**ENTER**.

Consequently, you will have the **Total Work Done (J)** as shown in the following image.

### 2. Applying SUMPRODUCT Function

Applying the **SUMPRODUCT function** is another smart way to do **Trapezoidal Integration in Excel**. The basic formula used here is the same as the previous method, but we will use an Excel function to calculate the integral value in just **1** step. Let’s use the steps outlined below to do this.

**Steps:**

- Firstly, use the following formula in cell
**D5**.

`=SUMPRODUCT(B6:B20-B5:B19,(C6:C20+C5:C19)/2)`

Here, the range **B6:B20** indicates the cells of the **Distance (m)** column, and the range **C6:C20** refers to the cells of the **Force (N)** column.

- Then, press
**ENTER**.

**Formula BreakdownÂ **

- Here,
**B6:B20-B5:B19**is theargument.*array1*- We subtracted array
**B5:B19**from the array**B6:B20**. - This indicates the 1st part of the
**Trapezoid Integration**formula**(b-a)**.

- We subtracted array
- Then,
**(C6:C20+C5:C19)/2**is theargument.*[array2]*- Here, we added the array
**C6:C20**andÂ**C5:C19**, then divided them by**2**. - It indicates the
**2nd**part of the**Trapezoid Integration**formula (f(p)+f(q)2).

- Here, we added the array
**Output**â†’**158**.

That’s it! Now, you will have the **Total Work Done (J)** in cell **D5** as demonstrated in the following picture.

### 3. Utilizing VBA Macro

We know that there is no in-built function to calculate the **Trapezoidal Integration** in Excel. So, we will create a custom function by using the **VBA Macro** feature of Excel. Let’s follow the procedure discussed in the following section.

**Steps:**

- Firstly, go to the
**Developer**tab from**Ribbon**. - After that, click on the
**Visual Basic**option from the**Code**group.

As a result, the **Microsoft Visual Basic** window will open on your worksheet.

- Now, in the
**Microsoft Visual Basic**window, go to the**Insert**tab. - Then, choose the
**Module**option from the drop-down.

- Next, write the following code in the newly created
**Module**.

```
Function TrapezoidalIntegration(x_values As Variant, y_values As Variant) As Variant
Dim i As Integer
For i = 1 To x_values.Rows.Count - 1
If IsNumeric(x_values.Cells(i)) = False Or IsNumeric(x_values.Cells(i + 1)) = False _
Or IsNumeric(y_values.Cells(i)) = False Or IsNumeric(y_values.Cells(i + 1)) = False Then
TrapezoidalIntegration = "Non-numeric value in the inputs"
Exit Function
End If
TrapezoidalIntegration = TrapezoidalIntegration + Abs(0.5 * (x_values.Cells(i + 1, 1) _
- x_values.Cells(i, 1)) * (y_values.Cells(i, 1) + y_values.Cells(i + 1, 1)))
Next i
End Function
```

**Code Breakdown**

- Firstly, we declared a function named
**TrapezoidalIntegration**and its data type ss**Variant**. - Inside the parentheses, we declared
**2**variables**x_Values**and**y_Values**, and both of their data types are as**Variant**. - Following that, we introduced another variable
**i**as**Integer**. - Then, we initiated a
**For Next**loop from**i = 1**to the**1**less of the number of rows that have the**x_values**. - After that, we used an
**IF statement**and in the**IF**statement, we used the**IsNumeric function**to check whether there are any non-numeric values or not. - If there are any non-numeric values in the inputs, the function will display
**Non-numeric value in the inputs**and it will terminate the function. - Then, we ended the
**IF**statement. - After that, we used the mathematical formula to calculate the
**Trapezoidal Integration**. - Next, we closed the
**For Next**loop. - Finally, we terminated the function.

- After writing the code, click on the
**Save**option.

- Following that, press the keyboard shortcut
**ALT + F11**to go to your worksheet. - Now, insert the following formula in cell
**D5**.

`=TrapezoidalIntegration(B5:B20,C5:C20)`

- Then, hit
**ENTER**.

Consequently, you will have the **Total Work Done (J)** as shown in the image below.

## Doing Integration by Applying the Midpoint Rule in Excel

In this section of the article, we will learn how to do integration by the **Midpoint Rule** in Excel. It is also known as **Simpson’s Rule.** Let’s say, we have a function * f(x) =(1+x^3)*, and we need to integrate it within the limits of

**1**to

**5**.

**âˆ« ^{5}_{1 }(1+x^3) dx**

If we calculate the integral manually, we get:

**[x+(x^4/4)] ^{5}_{1 }=(5+(5^4/4)) – (1+(1^4/4)) = 160Â **

This is the **Final Integral Value** calculated **manually**. Now, let’s follow the steps mentioned below and find out how close we can get to the **Final Integral Value** by using the **Midpoint Rule in Excel**. Here, is the blank dataset that we will use to do this.

**Step 01: Calculate dx Value**Â

- Firstly, enter the following formula in cell
**C22**.

`=(5-1)/15`

Here, **5** and **1** represent the **b **and **a **values respectively which are the **upper bound **and the **lower bound** of the integral. And **15** is the number of steps **(n Value)**.

- Following that, press
**ENTER**.

As a result, you will get the following output in cell **C22**.

- Now, enter the formula given below in cell
**C23**.

`=C22/2`

Here, cell **C22** represents the value of **dx**.

- Then, press
**ENTER**.

Consequently, you will have the following output in cell **C23**.

**Step 02: Find the x Value**

- Firstly, insert the following formula in cell
**C5**.

`=1+C23`

Here, cell **C23** indicates the value of **dx/2**.

- Then, hit
**ENTER**.

As a result, you will find the **x Value** for the **1st** step as shown in the following image.

- After that, use the formula given below in cell
**C6**.

`=C5+$C$22`

- Now, press
**ENTER**.

Afterward, you will have the **x Value** for the **2nd** step.

- Next, drag the
**Fill Handle**up to cell**C19**to obtain the**x Values**for the remaining steps.

**Step 03: Compute the f(x) Value**

- To compute the
**f(x) Value**, enter the following formula in cell**D5**.

`=(1+(C5^3))`

Here, cell **C5** refers to the **x Value** of the **1st** step.

- After that, press
**ENTER**.

Subsequently, you will get the **f(x) Value** for the **1st** step as shown in the image given below.

- At this stage, by dragging the
**Fill Handle**, you can get the rest of the**f(x) Values**.

**Step 04: Calculate the Final Integral Value**

- Firstly, apply the formula mentioned below in cell
**E5**.

`=D5*$C$22`

Here, cell **D5** indicates the **f(x) Value** for the **1st** step.

- Following that, press
**ENTER**.

As a result, you will have the **Integral Value** for the **1st** step.

- Now, drag the
**Fill Handle**to get the remaining**f(x) Values**as marked in the following image.

- After that, enter the following formula in cell
**E20**.

`=SUM(E5:E19)`

Here, the range **E5:E19** refers to the cells of the **Integral Value** column.

- Then, hit
**ENTER**.

Consequently, you will have the **Final Integral Value** in cell **E20** as demonstrated in the following picture.

Here, we can see that the** Final Integral Value** of the function is **159.787** which is really close to the **Final Integral Value** that we calculated **manually** (**160**).

## Practice Section

In the Excel Workbook, we have provided a **Practice Section **on the right side of the worksheet. Please practice it by yourself.

**Download Practice Workbook**

## Conclusion

These are all basic, easy, and convenient techniques you can apply anytime in spreadsheets to **do** **Trapezoidal Integration in Excel**. I hope this article has helped you to learn all the methods to count cells with texts. If you have any questions or feedback, then please leave a comment here. You can also have a glance at our more interesting and informative articles on Excel functions and applications on this website.

**<< Go Back to | Calculus in Excel | Excel for Math | Learn Excel**