Integration of a function over some interval

[jens1234]

Is there a function in excel which makes it possible to get the value of a function integrated over a defined interval?

[shg]

There are various functions that are integrated for particular purposes (like the probability mass function to get thec cumulative distribution), but unless you need a particular one, you're on your own.

Here's a simple Simpson integrator:

Function SimpsonInt(a As Double, b As Double, ByVal n As Long) As Double
    ' Returns the integral of Func (below) from a to b _
      using Composite Simpson's Rule over n intervals

    Dim i       As Double  ' index
    Dim dH      As Double  ' step size
    Dim dOdd    As Double  ' sum of Func(i), i = 1, 3, 5, 7, ... n-1, i.e., n/2 values
    Dim dEvn    As Double  ' sum of Func(i), i =   2, 4, 6,  ... n-2  i.e., n/2 - 1 values
    ' 1 + (n/2) + (n/2 - 1) + 1 = n+1 function evaluations

    If n < 1 Then Exit Function
    If n And 1 Then n = n + 1 ' n must be even
    dH = (b - a) / n

    For i = 1 To n - 1 Step 2
        dOdd = dOdd + Func(a + i * dH)
    Next

    For i = 2 To n - 2 Step 2
        dEvn = dEvn + Func(a + i * dH)
    Next

    SimpsonInt = (Func(a) + 4# * dOdd + 2# * dEvn + Func(b)) * dH / 3#    ' weighted sum
End Function

Function Func(x As Double) As Double
    ' replace this function with the function to be integrated
    Func = Sin(x)
End Function