How to Do Trapezoidal Integration in Excel (3 Suitable Methods)

Trapezoidal Integration is one of the 2 methods of Numerical Integration in Excel. In Excel, there is no in-built 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.


Download Practice Workbook


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.

qp f(x) dx

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

qp f(x) dx=(q-p)*(f(p)+f(q))/2


3 Simple Methods to Do Trapezoidal Integration in Excel

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).

excel trapezoidal integration

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.

Using Mathematical Formula to do Trapezoidal Integration in Excel

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.

Using the SUM function to do Trapezoidal Integration in Excel

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

Final output of method 1 to do Trapezoidal Integration in Excel


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 the array1 argument.
    • We subtracted array B5:B19 from the array B6:B20.
    • This indicates the 1st part of the Trapezoid Integration formula (b-a).
  • Then, (C6:C20+C5:C19)/2 is the [array2] argument.
    • 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).
  • Output 158.

Applying SUMPRODUCT Function to do Trapezoidal Integration in Excel

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

Final output of method 2 to do Trapezoidal Integration in Excel


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.

Utilizing VBA Macro to do Trapezoidal Integration in Excel

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

Writing VBA code to do Trapezoidal Integration in Excel

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.

Final output of method 3 to do Trapezoidal Integration in Excel


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.

51 (1+x^3) dx

If we calculate the integral manually, we get:

[x+(x^4/4)]51 =(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.

Integration by Midpoint Rule in Excel

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.

Calculate dx Value to do Integration by Midpoint Rule in Excel

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.

Find the x Value to do Integration by Midpoint Rule in Excel

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.

Using Fill Handle to do Integration by Midpoint Rule in Excel

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.

Compute the f(x) Value to do Integration by Midpoint Rule in Excel

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.

 Calculate the Final Integral Value to do Integration by Midpoint Rule in Excel

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.

Using the SUM function to do Integration by Midpoint Rule in Excel

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

Final output of method 4 to do Integration by Midpoint Rule in Excel

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.

practice section to do Trapezoidal Integration in Excel


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.


Related Articles

Zahid Hasan

Zahid Hasan

Hello and welcome! Thank you for visiting my profile. I am currently employed as an Excel & VBA Content Creator at ExcelDemy. My most recent academic qualification is a BSc (Eng) from the Bangladesh University of Engineering and Technology. Industrial and Production Engineering was my major. I constantly attempt to think creatively and find a simple answer.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo