A common complain about Excel is that it doesn’t provide a direct method to calculate the integral of a function. If the function is represented as 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 widespread 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:
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:”
Calculate the area under a curve/the integral of a function
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:
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 difficult part is the array/range definition. If n curve points (x, y) are known, the function can be written:
In the sample workbook the SUMPRODUCT function is used with the following ranges:
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:
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):
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!
In order 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 specific 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!
The accuracy of trapezoidal rule is strongly 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.
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 correct one? The answer is simple: for the specific 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:
To sum up, although the trapezoidal rule is an easy 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 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
The file can be opened with Excel 2007 or newer. Please enable macros before using it.
Numerical Integration In Excel Using The Composite Simpson's Rule
Calculating The Area Of A Simple Polygon Using The Shoelace Algorithm