I wrote a VBA function to implement Simpson's rule. This finds the area under a curve between two points without evaluating an integral analyticaly. A couple people have posted messages on this bulletin board asking how to do that so, here's my solution:
Function Simpson(a As Double, b As Double, n As Integer) As Double
'This function calculates the area under the curve y(x) from
'x=a to x=b using Simpson's rule with n intervals
'Note: n must be even
'
'Local variables
Dim h As Double, sum As Double, term As Double
Dim x As Double
Dim i As Integer
'Do error checking
If n = 0 Or n Mod 2 = 1 Then
Simpson = 0#
MsgBox "Sorry # of intervals has to be > 0 and even"
Exit Function
End If
h = (b - a) / n
x = a
sum = 0#
For i = 1 To n Step 2
term = y(x) + 4 * y(x + h) + y(x + 2 * h)
sum = sum + term
x = x + 2 * h
Next i
Simpson = sum * h / 3
End Function
Function y(x)
'This function computes the pdf of the standard normal
'distribution at x.
y = Application.NormDist(x, 0, 1, False)
End Function
With n = 10, Simpson gives the same answer as Excel's normdist function so, I think my code works. Try it! Any comments are welcome.
Thanks,
- Tom Wellington
PS I created the Simpson function by "jazzing up" the Integral function on page 195 of Bernard Liengme's book (see reference below). Thanks to professor Liengme for letting me post my modification here.
Reference: Bernard V. Liengme, A Guide to Microsoft Excel for Scientists and Engineers. 2nd Edition, 2000. Woborn, MA
Function Simpson(a As Double, b As Double, n As Integer) As Double
'This function calculates the area under the curve y(x) from
'x=a to x=b using Simpson's rule with n intervals
'Note: n must be even
'
'Local variables
Dim h As Double, sum As Double, term As Double
Dim x As Double
Dim i As Integer
'Do error checking
If n = 0 Or n Mod 2 = 1 Then
Simpson = 0#
MsgBox "Sorry # of intervals has to be > 0 and even"
Exit Function
End If
h = (b - a) / n
x = a
sum = 0#
For i = 1 To n Step 2
term = y(x) + 4 * y(x + h) + y(x + 2 * h)
sum = sum + term
x = x + 2 * h
Next i
Simpson = sum * h / 3
End Function
Function y(x)
'This function computes the pdf of the standard normal
'distribution at x.
y = Application.NormDist(x, 0, 1, False)
End Function
With n = 10, Simpson gives the same answer as Excel's normdist function so, I think my code works. Try it! Any comments are welcome.
Thanks,
- Tom Wellington
PS I created the Simpson function by "jazzing up" the Integral function on page 195 of Bernard Liengme's book (see reference below). Thanks to professor Liengme for letting me post my modification here.
Reference: Bernard V. Liengme, A Guide to Microsoft Excel for Scientists and Engineers. 2nd Edition, 2000. Woborn, MA