Re: Simpson's rule in Excel
- From: "David J. Braden" <dbraden@xxxxxxxxxxxxxx>
- Date: Mon, 23 Jan 2006 10:41:56 -0800
James, From an old post of mine --- "Area under a curve"
To use Excel for evaluating the integral of, say, 2+3*(Ln(x))^0.6 using Simpson's Rule (also see notes below):
1) Enter some labels: In cell.....enter A1 "X_1" A2 "X_n" A3 "NbrPanels"
2) Set some values: In cell.....enter B1 1 B2 2.5 B3 1000
3) Define some names: Select A1:B3, then choose Insert->Name->Create. Make sure that only the "Left Column" box is selected. If it isn't, you might have entered text instead of numbers in the right column, or mis-selected the range. Press OK.
4) Choose Insert->Names->Define Enter each of the following names and their definitions, pressing Add with each entry (you can copy and paste these): EPanels =NbrPanels+MOD(NbrPanels,2) delta =(X_n-X_1)/EPanels Steps =ROW(INDIRECT("1:"&EPanels+1))-1 EvalPts =X_1+delta*Steps SimpWts =IF(MOD(Steps,EPanels)=0,1,IF(MOD(Steps,2)=1,4,2))*delta/3
(optional) If interested in a trapezoidal approximation, define TrapWts =IF(MOD(Steps,EPanels)=0,0.5*delta,delta)
5) Close the Define Names box, and in, say, cell D1, array-enter =SUM(SimpWts*(2+3*LN(EvalPts)^0.6)) That is, type in the function, and hold ctl-shift when pressing Enter.
In general, ctrl-shift-enter =SUM(SimpWts*f(EvalPts)) where f() is a legitimate Excel expression that yields a scalar numeric value.
To use the trapezoidal method, substitute in the above expression TrapWts for SimpWts.
Notes: (1) With this implementation, an odd number for NbrPanels doesn't cut it (for Simpson's rule), so there will be no improvement moving from an odd number to the next integer (odd ones are automatically changed to the next even, internally).
(2) If you have "jumps" in your function, break it up at the points where those occur, and add the pieces.
.... FWIW, consider Weddle weights instead of the Simpson ones. For a single partition, they run [1 5 1 6 1 5 1], with the whole shebang multiplied by (3/10). Error term is much tighter, the calc is just as fast.
HTH Dave Braden
James Silverton wrote:
There have been no responses to a previous post but forgive me if I try again. If this is not regarded as a charting question, please let me know. Anyway, is it possible to write a statement evaluating the area under a graph by Simpson's rule without using VBA or helper columns?
James Silverton Potomac, Maryland, USA
- Simpson's rule in Excel
- From: James Silverton
- Simpson's rule in Excel
- Prev by Date: Re: Chart two variables with differing values
- Next by Date: Re: Very Simple Question
- Previous by thread: Re: Simpson's rule in Excel
- Next by thread: Re: how do i create a cumulative frequencey graph using excel?