Quick Jump
Introduction | Example | Tutorial | Applications | Comments

Introduction - Trapezoidal Rule Excel

Use this Trapezoidal Rule Excel Function to approximate the definite integral of paired data sets. A VBA Excel function to find the area under a curve is useful in engineering, business, finance and many scientific fields.

The trapezoidal rule is one of several ways to perform numerical integration on raw data. This tutorial will show you how to use the trapezoidal rule in Excel.

Example - Trapezoidal Rule Excel

Trapezoidal Rule Excel Function

Function TrapIntegration(KnownXs As Variant, KnownYs As Variant) As Variant
'------------------------------------------------------------------------------------------------------
'---DESCRIPTION: Approximates the integral using trapezoidal rule.-------------------------------------
'---CREATED BY: Ryan Wells-----------------------------------------------------------------------------
'---INPUT: KnownXs is the range of x-values. KnownYs is the range of y-values.-------------------------
'---OUTPUT: The output will be the approximate area under the curve (integral).------------------------
'------------------------------------------------------------------------------------------------------

    Dim i As Integer
    Dim bYrows As Boolean, bXrows As Boolean

'------------------------------------------------------------------------------
'I. Preliminary Error Checking
'------------------------------------------------------------------------------
On Error GoTo TrapIntError:
    'Error 1 - Check if the X values are range.
    If Not TypeName(KnownXs) = "Range" Then
        TrapIntegration = "Invalid X-range"
        Exit Function
    End If

    'Error 2 - Check if the Y values are range.
    If Not TypeName(KnownYs) = "Range" Then
        TrapIntegration = "Invalid Y-range"
        Exit Function
    End If


    'Error 3 - dimensions aren't even
    If KnownYs.Count <> KnownXs.Count Or _
       KnownYs.Rows.Count <> KnownXs.Rows.Count Or _
       KnownYs.Columns.Count <> KnownXs.Columns.Count Then
        TrapIntegration = "Known ranges are different dimensions."
        Exit Function
    End If
     
    'Error 4 - known Ys are not Nx1 or 1xN dimensions
    If KnownYs.Rows.Count <> 1 And KnownYs.Columns.Count <> 1 Then
        TrapIntegration = "Known Y's should be in a single column or a single row."
        Exit Function
    End If
     
    'Error 5 - known Xs are not Nx1 or 1xN dimensions
    If KnownXs.Rows.Count <> 1 And KnownXs.Columns.Count <> 1 Then
        TrapIntegration = "Known X's should be in a single column or a single row."
        Exit Function
    End If
     
    'Error 6 - Check for non-numeric KnownYs
    If KnownYs.Rows.Count > 1 Then
        bYrows = True
        For i = 1 To KnownYs.Rows.Count
            If IsNumeric(KnownYs.Cells(i, 1)) = False Then
                TrapIntegration = "One or all Known Y's are non-numeric."
                Exit Function
            End If
        Next i
    ElseIf KnownYs.Columns.Count > 1 Then
        bYrows = False
        For i = 1 To KnownYs.Columns.Count
            If IsNumeric(KnownYs.Cells(1, i)) = False Then
                TrapIntegration = "One or all KnownYs are non-numeric."
                Exit Function
            End If
        Next i
    End If
     
    'Error 7 - Check for non-numeric KnownXs
    If KnownXs.Rows.Count > 1 Then
        bXrows = True
        For i = 1 To KnownXs.Rows.Count
            If IsNumeric(KnownXs.Cells(i, 1)) = False Then
                TrapIntegration = "One or all Known X's are non-numeric."
                Exit Function
            End If
        Next i
    ElseIf KnownXs.Columns.Count > 1 Then
        bXrows = False
        For i = 1 To KnownXs.Columns.Count
            If IsNumeric(KnownXs.Cells(1, i)) = False Then
                TrapIntegration = "One or all Known X's are non-numeric."
                Exit Function
            End If
        Next i
    End If

'------------------------------------------------------------------------------
'II. Perform Trapezoidal Integration
'------------------------------------------------------------------------------
    TrapIntegration = 0

    'Apply the trapezoid rule: (y(i+1) + y(i))*(x(i+1) - x(i))*1/2.
    'Use the absolute value in case of negative numbers.
    If bXrows = True Then
        For i = 1 To KnownXs.Rows.Count - 1
            TrapIntegration = TrapIntegration + Abs(0.5 * (KnownXs.Cells(i + 1, 1) _
            - KnownXs.Cells(i, 1)) * (KnownYs.Cells(i, 1) + KnownYs.Cells(i + 1, 1)))
        Next i
    Else
        For i = 1 To KnownXs.Columns.Count - 1
            TrapIntegration = TrapIntegration + Abs(0.5 * (KnownXs.Cells(1, i + 1) _
            - KnownXs.Cells(1, i)) * (KnownYs.Cells(1, i) + KnownYs.Cells(1, i + 1)))
        Next i
    End If
Exit Function

TrapIntError:
    TrapIntegration = "Error Encountered: " & Err.Number & ", " & Err.Description
End Function

Tutorial - Trapezoidal Rule Excel

  1. Excel Macro Tutorial
    1. Open Visual Basic for Applications Editor
      Two options
      1. Press “Alt+F11”
        or
      2. Click “Visual Basic” on the Developer tab.
    2. From the Editor, click “Insert” at the top, then “Module”
      1. You can rename the module in the Properties window, if you like.
        The default name is Module1
    3. Paste Trapezoidal Rule Excel Function example to the editor
    4. Save and exit the editor
  2. Use Trapezoidal Rule Excel Function
    1. From Excel, type =TrapIntegration(KnownXs, KnownYs) into a blank cell where KnownXs and KnownYs are the ranges of your x-axis data and y-axis data.

      Trapezoidal Rule Excel

In the above example, =TrapIntegration(A2:A4,B2:B4) performs numerical integration on the data to yield a result of 3.

Application Ideas - Trapezoidal Rule Excel

It’s a fruitless task to try to name the applications where the trapezoidal rule can be used. The possibilities are endless. You can use numerical integration to solve billions of problems. Any time you want to approximate the area under a curve, the trapezoidal rule can be used.

One example of where I used this trapezoidal rule excel function in my nuclear engineering job was to approximate the integral of Brunswick Nuclear Plant’s LPRM (Local Power Range Monitor) responses. I used this an alternate means of characterizing the health of our control blades - a term we dubbed “Watt-Days.”

Comments

Now it’s your turn! Leave a comment and let me know how I can help you. Remember - Automate Responsibly.


wellsrPRO

Coming Fall 2017

Auto-Import Macros Directly from wellsr.com

Absolutely FREE when you join the waitlist


About Ryan Wells


Ryan Wells

Ryan Wells is a Nuclear Engineer and professional VBA Developer. He is the lead developer of several VBA applications, including PDF VBA - a leading Excel Add-in for exporting Excel Objects, like charts and tables, to PDFs. Discover more of his popular Excel Add-ins, including Mouse To Macro and CF Shapes, at his dedicated Excel Add-ins page.