my given
x | 0 | 20| 40 |60 |80 |100 |120|
y| 0 |7.4 |11.8 |15.2 |15| 9.2 |0.2|
h= 20 n= 6 segments
Does the assignment require a VBA solution?
I don't know why you would both with a VBA subroutine with
hardcoded values. Such calculations can be done with Excel formulas directly.
Are you required to provide a general solution for any h and any n+1 data?
Even if so, I would implement a parameterized VBA function, not a subroutine.
-----
Moreover, are you sure that you should implement Simpson's 3/8 rule
per se?
The data that you provide -- h=20 and n=6 -- is suspiciously exactly what we need to calculate
Composite Simpson's 3/8 rule.
Moreover, the loop that you prototype in your VBA procedure applies to
Composite Simpson's 3/8 rule, not to Simpson's 3/8 rule.
By coincidence, the same data is also sufficient to calculate Simpson's 3/8 rule. But Simpson's 3/8 rule has only 4 terms, not n+1 terms like your loop.
The definitions and formulas for the various Simpson's rule can be found in the Simpson's rule wikipage (
click here).
For Simpon's 3/8 rule:
Note that we require f(x) at x = (2*a+b)/3 and x = (a+2*b)/3. For a=0 and b=120, those are x=40 and x=80. And fortunately, we are given f(40) and f(80), which are the corresponding values of y.
For Composite Simpson's 3/8 rule:
It might helpful to start by charting the data that you are given.
The goal is to calculate the area under that "curve" (collection of line segments).
(Do you really want the area under a best-fit curve? An order-6 polynomial trendline can fit 7 data points exactly. I hope to provide that in a later response. But I will stick with this for now, since it might be what you want, anyway.)
The following shows one implementation, using just Excel.
Rich (BB code):
Formulas:
D3: =((A10-A4)/8) * (B4 + 3*B6 + 3*B8 + B10)
D4: =((A10-A4)/8) * (B4 + 3*VLOOKUP((2*A4+A10)/3, A4:B10, 2, 0) + 3*VLOOKUP((A4+2*A10)/3, A4:B10, 2, 0) + B10)
D6: =(3*(A10-A4)/6/8) * (B4 + 3*B5 + 3*B6 + 2*B7 + 3*B8 +3*B9 + 2*B10)
D8: =SUMPRODUCT(A5:A10-A4:A9, B5:B10+B4:B9)/2
D8 uses the trapezoid rule, another common method. It is provided a check, to demonstrate the reasonableness of the results of the Simpson's rules. It is not part of the solution, for your purposes.
D3 is a straight-forward implementation of the (regular) Simpson's 3/8 rule. It references B6 and B8 directly, which are f(40) and f(80).
D4 is a more-general form of the same formula. It uses VLOOKUP to find the values of y (f(x)) that correspond to x = (2*a+b)/3 and x=(a+2*b)/3.
D6 is a simplified implementation of the Composite Simpson's 3/8 rule. It is tailored to the 7 pairs of data that you provide.
We can provide a more general implementation for any set of n+1 data. That would be more straight-forward to implement with a VBA function.
But I will wait for you to clarify the assignment, answering my questions above, before investing more time.
I hope you find this informative and useful and progressing toward to the solution that you require.