Help!

Simpson's rule in Excel

 
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> Charting RSS
Next:  How do a put two pies (series) in one chart  
Author Message
James Silverton
External


Since: Dec 07, 2005
Posts: 148



PostPosted: Mon Jan 23, 2006 3:37 pm    Post subject: Simpson's rule in Excel
Archived from groups: microsoft>public>excel>charting (more info?)

Hello, All!

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
Back to top
David J. Braden
External


Since: Oct 11, 2004
Posts: 29



PostPosted: Mon Jan 23, 2006 3:41 pm    Post subject: Re: Simpson's rule in Excel [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

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:
> Hello, All!
>
> 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
Back to top
Bernard Liengme
External


Since: Jan 27, 2004
Posts: 2613



PostPosted: Mon Jan 23, 2006 5:14 pm    Post subject: Re: Simpson's rule in Excel [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)

Please look at my screen shot of a worksheet on
http://www.stfx.ca/people/bliengme/ExcelTips/AreaUnderCurve.htm

I can generate the same result with:
=(1/3)*(A6-A5)*SUMPRODUCT(--(MOD(ROW(A5:B9),2)=1),((B5:B9)+4*(B6:B10)+B7:B11))

If you look at the screen shot you will see that my x values are in A5:A11,
y-values in B5:B11
When I use a 'helper column' I need formulas in C5, C7, C9; i.e. in odd
cells hence the MOD(ROW(),2)=1 test



Hope this helps
--
Bernard V Liengme
www.stfx.ca/people/bliengme
remove caps from email


"James Silverton" wrote in message

> Hello, All!
>
> 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
Back to top
Display posts from previous:   
Post new topic   General Reply to Topic (not reply to a specific post)    Forums Home -> Charting All times are: Eastern Time (US & Canada)
Page 1 of 1

 
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum