copy

Thursday, 13 June 2013



Numerical Integration In Excel Using The Trapezoidal Rule

The Basics

A common complaint about Excel is that it doesn’t provide a direct method to calculate the integral of a function. If the function is represented as a curve in a chart, then the integral is defined to be the (net signed) area under that curve. So, if you have to calculate the area under a curve, you must think of an indirect way to do it. One popular method for accomplishing this task is the so-called trapezoidal rule.
 
According to Wikipedia: “The trapezoidal rule is a technique for approximating the definite integral:

Integral f(x)

The trapezoidal rule works by approximating the region under the graph of the function f(x) as a trapezoid and calculating its area. It follows that:”

Trapezoidal Rule - Formula


Calculate the area under a curve/the integral of a function

Trapezoidal Rule

1st method: Spreadsheet calculations

Numerical Integration In Excel - Spreadsheet Calculations

If you know n points (x, y) from the curve you can apply the previous equation n-1 times and then sum the results. In the sample workbook, for example, we had the function y = 4*x^2, we knew 10 points, so we applied the formula 9 times. For the first point the result was  (1 – 0)*(4 + 0)/2 = 2, for the second (2 – 1)*(16 + 4)/2 = 10 and so on. The picture above contains the entire set of calculations.


2nd method: SUMPRODUCT formula

With this method, you avoid the intermediate calculations, and by using only one function, you get the result. However, the level of difficulty is a little bit higher than the first method (especially if you are new to Excel). The method involves the SUMPRODUCT function, the syntax of which is given below:

SUMPRODUCT(array1, [array2], [array3], ...)

The SUMPRODUCT function multiplies the corresponding components in the given arrays and returns the sum of these products. Array1, array2... are the ranges of cells or arrays that you wish to multiply. All arrays must have the same number of rows and columns, and you must enter at least 2 arrays (you can have up to 30 arrays).

The tricky part is the array/range definition. If n curve points (x, y) are known, the function can be written:

Numerical Integration In Excel - SUMPRODUCT Function

In the sample workbook the SUMPRODUCT function is used with the following ranges:

=SUMPRODUCT(A5:A13-A4:A12;(B5:B13+B4:B12)/2).

In reality, we applied the same function as in method 1, but instead of single cells, we had multiple cells/arrays. The function performs the following calculation:

SUMPRODUCT Function - Trapezoidal Rule

Without any doubt, the second method is much more straightforward than the first one.


3rd method: Custom VBA function

At your Excel file, switch to VBA editor (ALT + F11), go to menu Insert Module and add the following lines of code.

Option Explicit

Function CurveIntegration(KnownXs As Variant, KnownYs As Variant) As Variant
    
    'Calculates the area under a curve using the trapezoidal rule.
    'KnownXs and KnownYs are the known (x,y) points of the curve.
    
    'By Christos Samaras
    'http://www.myengineeringworld.net
    
    Dim i As Integer
    
    'Check if the X values are range.
    If Not TypeName(KnownXs) = "Range" Then
        CurveIntegration = "Xs range is not valid"
        Exit Function
    End If
    
    'Check if the Y values are range.
    If Not TypeName(KnownYs) = "Range" Then
        CurveIntegration = "Ys range is not valid"
        Exit Function
    End If
    
    'Check if the number of X values are equal to the number of Y values.
    If KnownXs.Rows.Count <> KnownYs.Rows.Count Then
        CurveIntegration = "Number of Xs <> Number of Ys"
        Exit Function
    End If
    
    CurveIntegration = 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.
    For i = 1 To KnownXs.Rows.Count - 1
        CurveIntegration = CurveIntegration + Abs(0.5 * (KnownXs.Cells(i + 1, 1) _
        - KnownXs.Cells(i, 1)) * (KnownYs.Cells(i, 1) + KnownYs.Cells(i + 1, 1)))
    Next i

End Function

The advantage of this custom function is that you don’t need to worry about the input ranges. The code checks if the x and y values are ranges, as well as if the x and y ranges are equal (for example 10 x values and 10 y values). If something goes wrong, the function returns an error message instead of the value. So, you only have to insert the input ranges in the function, and the function returns the curve area. In the sample workbook, for example, the custom function was used with the following ranges (we had 10 x values and 10 y values – 10 curve points):

=CurveIntegration(A4:A13;B4:B13)


Extract chart data

Until now you have learned 3 ways of calculating the area under a curve. However, all methods require input values/ranges. What if you don’t know the input data/curve points (the series data)? I am sure that you wonder how is it possible to have a workbook with a chart, but without having the series data. Let me say that is not impossible. If you receive for example an email with a workbook that contains a chart the series data of which come from other workbooks that you didn’t receive, then you are in trouble!

To avoid situations like this, I have written the following VBA procedure which loops through all series of the selected chart and extracts their data to a new worksheet named “Chart Data.”

Sub ExtractChartData()

    'Extracts all the data from all the series of the active chart.
    'It writes the data to a new worksheet named "Chart Data".
    
    'By Christos Samaras
    'http://www.myengineeringworld.net
    
    Dim MyChart As Chart
    Dim sht As Worksheet
    Dim i As Long
    Dim MySeries As Series
    
    'Check if a chart is selected.
    If ActiveChart Is Nothing Then
        MsgBox "No chart was selected!" & vbCr & "Please select a chart and retry...", vbCritical, "Chart Error"
        Exit Sub
    End If
    
    'Stop screen flickering.
    Application.ScreenUpdating = False
    
    Set MyChart = ActiveChart
    
    'Add the new worksheet.
    Set sht = Worksheets.Add
    
    On Error Resume Next
    
    'Rename the new worksheet and change its tab's color.
    With sht
        .Name = "Chart Data"
        .Tab.Color = vbRed
        .Activate
    End With
        
    'Loop through all the series and extract their data.
    For i = 1 To MyChart.SeriesCollection.Count
        
        Set MySeries = MyChart.SeriesCollection(i)
        
        With sht
            .Cells(1, 2 * i).Value = MySeries.Name
            .Cells(1, 2 * i).Font.Bold = True
            .Cells(2, 2 * i - 1).Resize(MySeries.Points.Count).Value = WorksheetFunction.Transpose(MySeries.XValues)
            .Cells(2, 2 * i).Resize(MySeries.Points.Count).Value = WorksheetFunction.Transpose(MySeries.Values)
        End With
        
    Next i
    
    'Autofit the columns that contain the data.
    'Here the last column 2*i is converted to a letter.
    Columns("A:" & Mid(Cells(1, 2 * i).Address, InStr(Cells(1, 2 * i).Address, "$") _
    + 1, InStr(2, Cells(1, 2 * i).Address, "$") - 2)).EntireColumn.AutoFit
    
    'Re-enable the screen.
    Application.ScreenUpdating = True
    
   'Inform the user that the macro finished.
    MsgBox "Chart data were extracted successfully!", vbInformation, "Done"
    
End Sub


Limitations of trapezoidal rule

As it was highlighted in the beginning, the trapezoidal rule is an approximate method to calculate the area under a curve/to perform numerical integration. In the sample workbook you will notice that, for the particular curve, all 3 different ways that were described above result in the same value (978). You might believe that since the 3 methods agree in the final value, this is the correct one. Unfortunately, this is not true!

Trapezoidal Rule - Double Step

The accuracy of trapezoidal rule is firmly related with the number of known curve points. The more points you know, the more the trapezoids, so the better the approximation (for a specific range). If in the specific example we knew 20 points (step 0.5) instead of 10 (step 1), then the result with 3 methods would be 973.5.

Trapezoidal Rule - Double Step Resutls

If we knew 901 points (step 0.01), then the result would be around 972, which is the correct one. How we know that 972 is the right one? The answer is simple: for the particular example, the curve was based on the function y = 4* x^2. For the x range 0 to 9 the integral of this function will be:

Integral Of 4x2

Trapezoidal Rule - Smaller Step Resutls

To sum up, although the trapezoidal rule is a simple way to calculate the area under a curve you should never forget that is an approximate method. The more points you have, the better the results you get.

Furthermore, be careful not to confuse the curve area with the value of the definite integral. They are not the same since area cannot be negative by definition! When you use the trapezoidal rule as a tool to calculate a curve area you have to be careful in cases where the curve is below the x or y-axis. If it is, then you should use the abs function (returns the absolute value of a number) in the first two methods to get the correct area, while the custom VBA function has already implemented this function. 


Download it from here

Download

The file can be opened with Excel 2007 or newer. Please enable macros before using it.


Read also

Numerical Integration In Excel Using The Composite Simpson's Rule
Calculating The Area Of A Simple Polygon Using The Shoelace Algorithm 

Did you like this post? If yes, then share it with your friends. Thank you!



Categories:


Mechanical Engineer (Ph.D. cand.), M.Sc. Cranfield University, Dipl.-Ing. Aristotle University, Thessaloniki - Greece.
Communication: e-mail, Facebook, Twitter, Google+ and Linkedin. More info