Hi
I have built a spreadsheet that can calculate the area under a curve of a
set of data but I would like to have this in VBA for Excel, in say
Integral(C1,C2) format or a button on the toolbar.
Can anyone point me in the right direction for acquiring the code?
Cheers
WayneL
"WayneL" <[email protected]> wrote...
>I have built a spreadsheet that can calculate the area under a curve
>of a set of data but I would like to have this in VBA for Excel, in
>say Integral(C1,C2) format or a button on the toolbar.
>
>Can anyone point me in the right direction for acquiring the code?
Numerical integration using cmputers isn't a novel concept. What would your
C1 and C2 be? In other words, what would these cells contain? Definite
integrals require two end points, but they also require a particular curve.
How would you specify the particular curve for your VBA procedure?
Hi
The C1 and C2 corresponds to column 1 & 2. I have x data in column 1 (C1)
and y data in C2. The start and end points of the integral in the beginning
and end of the data in both C1 and C2. I should really say I want to find
the apx area under the curve this data corresponds to.
Cheers
WayneL
"Harlan Grove" <[email protected]> wrote in message
news:[email protected]...
> "WayneL" <[email protected]> wrote...
>>I have built a spreadsheet that can calculate the area under a curve
>>of a set of data but I would like to have this in VBA for Excel, in
>>say Integral(C1,C2) format or a button on the toolbar.
>>
>>Can anyone point me in the right direction for acquiring the code?
>
> Numerical integration using cmputers isn't a novel concept. What would
> your C1 and C2 be? In other words, what would these cells contain?
> Definite integrals require two end points, but they also require a
> particular curve. How would you specify the particular curve for your VBA
> procedure?
>
WayneL wrote:
> The C1 and C2 corresponds to column 1 & 2. I have x data in column 1 (C1)
> and y data in C2. The start and end points of the integral in the beginning
> and end of the data in both C1 and C2. I should really say I want to find
> the apx area under the curve this data corresponds to.
Simplest way is to take vertical slices and add them up. For example:
for each row, vertical slice = average(y1, y2)/(x2-x1)
Then sum up all slices to get the total area under the curve.
"WayneL" <[email protected]> wrote...
>The C1 and C2 corresponds to column 1 & 2. I have x data in column
>1 (C1) and y data in C2. The start and end points of the integral
>in the beginning and end of the data in both C1 and C2. I should
>really say I want to find the apx area under the curve this data
>corresponds to.
....
You don't need VBA. Replacing the C1 and C2 references with the more
immediately understandable X and Y,
Trapezoid Rule:
=SUMPRODUCT(
INDEX(Y,1):INDEX(Y,ROWS(Y)-1)+INDEX(Y,2):INDEX(Y,ROWS(Y)),
INDEX(X,2):INDEX(X,ROWS(X))-INDEX(X,1):INDEX(X,ROWS(X)-1)
)/2
Simpson's 3/8 Rule wouldn't be all that difficult to implement if X were
uniformly graduated.
Bernard Liengme discusses simple numerical integration at
http://www.stfx.ca/people/bliengme/E...UnderCurve.htm
More complex formulas with error bounds are given by
http://www.convertit.com/Go/ConvertI...s=150&Page=875
If the function is analytically integrable, that will always be more
accurate than numerical integration
Jerry
WayneL wrote:
> Hi
>
>
> The C1 and C2 corresponds to column 1 & 2. I have x data in column 1 (C1)
> and y data in C2. The start and end points of the integral in the beginning
> and end of the data in both C1 and C2. I should really say I want to find
> the apx area under the curve this data corresponds to.
>
>
>
> Cheers
>
> WayneL
>
> "Harlan Grove" <[email protected]> wrote in message
> news:[email protected]...
>
>>"WayneL" <[email protected]> wrote...
>>
>>>I have built a spreadsheet that can calculate the area under a curve
>>>of a set of data but I would like to have this in VBA for Excel, in
>>>say Integral(C1,C2) format or a button on the toolbar.
>>>
>>>Can anyone point me in the right direction for acquiring the code?
>>>
>>Numerical integration using cmputers isn't a novel concept. What would
>>your C1 and C2 be? In other words, what would these cells contain?
>>Definite integrals require two end points, but they also require a
>>particular curve. How would you specify the particular curve for your VBA
>>procedure?
> ...I want to find the apx area under the curve this data corresponds to.
Hi. Not sure if this would help. If you data apr. fits a polynomial curve,
then perhaps this may be an alternative.
I can't tell from you description, but I assume you have an "x-y scatter
chart" ?? Here, I assume your data has range names "x" & "y". Maybe this
will give you some ideas to fine what works for your data.
Sub Demo()
Dim v
Dim a, b, c
Dim x
Dim Area
v = [Linest(y,x^{1,2})]
a = v(1)
b = v(2)
c = v(3)
x = [Max(x)]
Area = (a * x ^ 3) / 3 + (b * x ^ 2) / 2 + c * x
x = [Min(x)]
Area = Area - ((a * x ^ 3) / 3 + (b * x ^ 2) / 2 + c * x)
End Sub
HTH :>)
--
Dana DeLouis
Win XP & Office 2003
"WayneL" <[email protected]> wrote in message
news:[email protected]...
> Hi
>
>
> The C1 and C2 corresponds to column 1 & 2. I have x data in column 1 (C1)
> and y data in C2. The start and end points of the integral in the
> beginning and end of the data in both C1 and C2. I should really say I
> want to find the apx area under the curve this data corresponds to.
>
>
>
> Cheers
>
> WayneL
>
> "Harlan Grove" <[email protected]> wrote in message
> news:[email protected]...
>> "WayneL" <[email protected]> wrote...
>>>I have built a spreadsheet that can calculate the area under a curve
>>>of a set of data but I would like to have this in VBA for Excel, in
>>>say Integral(C1,C2) format or a button on the toolbar.
>>>
>>>Can anyone point me in the right direction for acquiring the code?
>>
>> Numerical integration using cmputers isn't a novel concept. What would
>> your C1 and C2 be? In other words, what would these cells contain?
>> Definite integrals require two end points, but they also require a
>> particular curve. How would you specify the particular curve for your VBA
>> procedure?
>>
>
>
Hi
I have a set of data that needs integrating but it does not fit an easy
function therefore I need some technique (fuction) like that seen in Flex
Pro. In this package you select X and Y and press a button titled "Area
under Curve". This software is expensive and I am sure this could be done
in Excel.
Cheers
WayneL
P.S
Some example data I am trying to find the Area Under the Curve.
Seconds Voltage
0.095 4.55E-04
2.526 4.14E-04
4.659 3.74E-04
6.791 3.45E-04
8.923 3.25E-04
11.055 3.11E-04
13.188 3.09E-04
15.321 3.05E-04
17.454 2.93E-04
19.587 2.86E-04
21.72 2.80E-04
23.855 2.76E-04
25.987 2.73E-04
28.119 2.70E-04
30.301 2.68E-04
32.433 2.66E-04
34.566 2.64E-04
36.698 2.62E-04
38.83 2.61E-04
41.172 2.60E-04
43.305 2.58E-04
45.436 2.57E-04
47.569 2.57E-04
49.702 2.56E-04
51.834 2.56E-04
53.966 2.56E-04
56.1 2.56E-04
58.233 2.56E-04
60.366 2.56E-04
62.499 2.55E-04
64.632 2.55E-04
66.772 2.55E-04
68.954 2.55E-04
71.086 2.55E-04
73.219 2.53E-04
75.357 2.54E-04
77.489 2.56E-04
79.621 2.55E-04
81.753 2.55E-04
83.884 2.55E-04
86.016 2.55E-04
88.149 2.55E-04
90.288 2.55E-04
92.42 2.55E-04
94.552 2.55E-04
96.684 2.55E-04
98.816 2.55E-04
100.949 2.56E-04
103.082 2.56E-04
105.214 2.56E-04
107.347 2.56E-04
109.549 2.55E-04
111.681 2.55E-04
113.813 2.54E-04
115.945 2.53E-04
118.077 2.53E-04
120.208 2.53E-04
122.341 2.53E-04
124.473 2.53E-04
126.605 2.53E-04
128.737 2.53E-04
130.87 2.53E-04
133.003 2.54E-04
135.136 2.54E-04
137.269 2.55E-04
139.401 2.53E-04
141.534 2.55E-04
143.666 2.57E-04
145.798 2.58E-04
147.93 2.59E-04
150.061 2.58E-04
152.193 2.59E-04
154.325 2.56E-04
156.457 2.57E-04
158.589 2.58E-04
160.723 2.57E-04
162.854 2.58E-04
164.986 2.60E-04
167.119 2.60E-04
169.251 2.61E-04
171.384 2.62E-04
173.517 2.63E-04
175.65 2.64E-04
177.785 2.67E-04
179.923 2.59E-04
182.055 2.59E-04
184.188 2.60E-04
186.327 2.60E-04
188.51 2.61E-04
190.649 2.60E-04
192.787 2.59E-04
194.918 2.60E-04
197.059 2.60E-04
199.2 2.60E-04
201.338 2.60E-04
203.476 2.61E-04
205.611 2.61E-04
207.744 2.62E-04
209.882 2.62E-04
212.02 2.63E-04
214.159 2.67E-04
216.292 2.65E-04
218.425 2.65E-04
220.557 2.65E-04
222.689 2.66E-04
224.821 2.66E-04
226.954 2.66E-04
229.094 2.67E-04
231.226 2.67E-04
233.359 2.66E-04
235.49 2.67E-04
237.622 2.67E-04
239.805 2.68E-04
241.938 2.69E-04
244.072 2.69E-04
246.204 2.70E-04
248.345 2.71E-04
250.478 2.71E-04
252.611 2.72E-04
254.744 2.73E-04
256.876 2.74E-04
259.009 2.75E-04
261.142 2.76E-04
263.274 2.75E-04
265.407 2.76E-04
267.539 2.76E-04
269.67 2.77E-04
271.802 2.77E-04
273.935 2.78E-04
276.355 2.79E-04
278.677 2.79E-04
280.937 2.81E-04
283.072 2.82E-04
285.205 2.83E-04
287.337 2.84E-04
289.47 2.84E-04
291.602 2.85E-04
293.743 2.86E-04
296.031 2.86E-04
298.163 2.87E-04
300.295 2.88E-04
***********************************************
"Dana DeLouis" <[email protected]> wrote in message
news:[email protected]...
>> ...I want to find the apx area under the curve this data corresponds to.
>
> Hi. Not sure if this would help. If you data apr. fits a polynomial
> curve, then perhaps this may be an alternative.
> I can't tell from you description, but I assume you have an "x-y scatter
> chart" ?? Here, I assume your data has range names "x" & "y". Maybe this
> will give you some ideas to fine what works for your data.
>
> Sub Demo()
> Dim v
> Dim a, b, c
> Dim x
> Dim Area
>
> v = [Linest(y,x^{1,2})]
> a = v(1)
> b = v(2)
> c = v(3)
>
> x = [Max(x)]
> Area = (a * x ^ 3) / 3 + (b * x ^ 2) / 2 + c * x
>
> x = [Min(x)]
> Area = Area - ((a * x ^ 3) / 3 + (b * x ^ 2) / 2 + c * x)
> End Sub
>
> HTH :>)
> --
> Dana DeLouis
> Win XP & Office 2003
>
>
> "WayneL" <[email protected]> wrote in message
> news:[email protected]...
>> Hi
>>
>>
>> The C1 and C2 corresponds to column 1 & 2. I have x data in column 1
>> (C1) and y data in C2. The start and end points of the integral in the
>> beginning and end of the data in both C1 and C2. I should really say I
>> want to find the apx area under the curve this data corresponds to.
>>
>>
>>
>> Cheers
>>
>> WayneL
>>
>> "Harlan Grove" <[email protected]> wrote in message
>> news:[email protected]...
>>> "WayneL" <[email protected]> wrote...
>>>>I have built a spreadsheet that can calculate the area under a curve
>>>>of a set of data but I would like to have this in VBA for Excel, in
>>>>say Integral(C1,C2) format or a button on the toolbar.
>>>>
>>>>Can anyone point me in the right direction for acquiring the code?
>>>
>>> Numerical integration using cmputers isn't a novel concept. What would
>>> your C1 and C2 be? In other words, what would these cells contain?
>>> Definite integrals require two end points, but they also require a
>>> particular curve. How would you specify the particular curve for your
>>> VBA procedure?
>>>
>>
>>
>
>
Put your data in A1 thru B141
In C2 put: =A2-A1 the width of the base
In D2 put: =(B2+B1)/2 the height of the slice
In E2 put: =C2*D2 the area of the slice
Copy C2,D2,E2 down
In E142 put: =SUM(E2:E141) the sum of the areas
You should see:
0.095 4.55E-04 base height area
2.526 4.14E-04 2.431 4.35E-04 1.06E-03
4.659 3.74E-04 2.133 3.94E-04 8.40E-04
6.791 3.45E-04 2.132 3.60E-04 7.66E-04
8.923 3.25E-04 2.132 3.35E-04 7.14E-04
11.055 3.11E-04 2.132 3.18E-04 6.78E-04
13.188 3.09E-04 2.133 3.10E-04 6.61E-04
15.321 3.05E-04 2.133 3.07E-04 6.55E-04
17.454 2.93E-04 2.133 2.99E-04 6.38E-04
19.587 2.86E-04 2.133 2.90E-04 6.18E-04
21.720 2.80E-04 2.133 2.83E-04 6.04E-04
23.855 2.76E-04 2.135 2.78E-04 5.94E-04
25.987 2.73E-04 2.132 2.75E-04 5.85E-04
28.119 2.70E-04 2.132 2.72E-04 5.79E-04
30.301 2.68E-04 2.182 2.69E-04 5.87E-04
32.433 2.66E-04 2.132 2.67E-04 5.69E-04
34.566 2.64E-04 2.133 2.65E-04 5.65E-04
36.698 2.62E-04 2.132 2.63E-04 5.61E-04
38.830 2.61E-04 2.132 2.62E-04 5.58E-04
41.172 2.60E-04 2.342 2.61E-04 6.10E-04
43.305 2.58E-04 2.133 2.59E-04 5.52E-04
45.436 2.57E-04 2.131 2.58E-04 5.49E-04
47.569 2.57E-04 2.133 2.57E-04 5.48E-04
49.702 2.56E-04 2.133 2.57E-04 5.47E-04
51.834 2.56E-04 2.132 2.56E-04 5.46E-04
53.966 2.56E-04 2.132 2.56E-04 5.46E-04
56.100 2.56E-04 2.134 2.56E-04 5.46E-04
58.233 2.56E-04 2.133 2.56E-04 5.46E-04
60.366 2.56E-04 2.133 2.56E-04 5.46E-04
62.499 2.55E-04 2.133 2.56E-04 5.45E-04
64.632 2.55E-04 2.133 2.55E-04 5.44E-04
66.772 2.55E-04 2.14 2.55E-04 5.46E-04
68.954 2.55E-04 2.182 2.55E-04 5.56E-04
71.086 2.55E-04 2.132 2.55E-04 5.44E-04
73.219 2.53E-04 2.133 2.54E-04 5.42E-04
75.357 2.54E-04 2.138 2.54E-04 5.42E-04
77.489 2.56E-04 2.132 2.55E-04 5.44E-04
79.621 2.55E-04 2.132 2.56E-04 5.45E-04
81.753 2.55E-04 2.132 2.55E-04 5.44E-04
83.884 2.55E-04 2.131 2.55E-04 5.43E-04
86.016 2.55E-04 2.132 2.55E-04 5.44E-04
88.149 2.55E-04 2.133 2.55E-04 5.44E-04
90.288 2.55E-04 2.139 2.55E-04 5.45E-04
92.420 2.55E-04 2.132 2.55E-04 5.44E-04
94.552 2.55E-04 2.132 2.55E-04 5.44E-04
96.684 2.55E-04 2.132 2.55E-04 5.44E-04
98.816 2.55E-04 2.132 2.55E-04 5.44E-04
100.949 2.56E-04 2.133 2.56E-04 5.45E-04
103.082 2.56E-04 2.133 2.56E-04 5.46E-04
105.214 2.56E-04 2.132 2.56E-04 5.46E-04
107.347 2.56E-04 2.133 2.56E-04 5.46E-04
109.549 2.55E-04 2.202 2.56E-04 5.63E-04
111.681 2.55E-04 2.132 2.55E-04 5.44E-04
113.813 2.54E-04 2.132 2.55E-04 5.43E-04
115.945 2.53E-04 2.132 2.54E-04 5.40E-04
118.077 2.53E-04 2.132 2.53E-04 5.39E-04
120.208 2.53E-04 2.131 2.53E-04 5.39E-04
122.341 2.53E-04 2.133 2.53E-04 5.40E-04
124.473 2.53E-04 2.132 2.53E-04 5.39E-04
126.605 2.53E-04 2.132 2.53E-04 5.39E-04
128.737 2.53E-04 2.132 2.53E-04 5.39E-04
130.870 2.53E-04 2.133 2.53E-04 5.40E-04
133.003 2.54E-04 2.133 2.54E-04 5.41E-04
135.136 2.54E-04 2.133 2.54E-04 5.42E-04
137.269 2.55E-04 2.133 2.55E-04 5.43E-04
139.401 2.53E-04 2.132 2.54E-04 5.42E-04
141.534 2.55E-04 2.133 2.54E-04 5.42E-04
143.666 2.57E-04 2.132 2.56E-04 5.46E-04
145.798 2.58E-04 2.132 2.58E-04 5.49E-04
147.930 2.59E-04 2.132 2.59E-04 5.51E-04
150.061 2.58E-04 2.131 2.59E-04 5.51E-04
152.193 2.59E-04 2.132 2.59E-04 5.51E-04
154.325 2.56E-04 2.132 2.58E-04 5.49E-04
156.457 2.57E-04 2.132 2.57E-04 5.47E-04
158.589 2.58E-04 2.132 2.58E-04 5.49E-04
160.723 2.57E-04 2.134 2.58E-04 5.50E-04
162.854 2.58E-04 2.131 2.58E-04 5.49E-04
164.986 2.60E-04 2.132 2.59E-04 5.52E-04
167.119 2.60E-04 2.133 2.60E-04 5.55E-04
169.251 2.61E-04 2.132 2.61E-04 5.55E-04
171.384 2.62E-04 2.133 2.62E-04 5.58E-04
173.517 2.63E-04 2.133 2.63E-04 5.60E-04
175.650 2.64E-04 2.133 2.64E-04 5.62E-04
177.785 2.67E-04 2.135 2.66E-04 5.67E-04
179.923 2.59E-04 2.138 2.63E-04 5.62E-04
182.055 2.59E-04 2.132 2.59E-04 5.52E-04
184.188 2.60E-04 2.133 2.60E-04 5.54E-04
186.327 2.60E-04 2.139 2.60E-04 5.56E-04
188.510 2.61E-04 2.183 2.61E-04 5.69E-04
190.649 2.60E-04 2.139 2.61E-04 5.57E-04
192.787 2.59E-04 2.138 2.60E-04 5.55E-04
194.918 2.60E-04 2.131 2.60E-04 5.53E-04
197.059 2.60E-04 2.141 2.60E-04 5.57E-04
199.200 2.60E-04 2.141 2.60E-04 5.57E-04
201.338 2.60E-04 2.138 2.60E-04 5.56E-04
203.476 2.61E-04 2.138 2.61E-04 5.57E-04
205.611 2.61E-04 2.135 2.61E-04 5.57E-04
207.744 2.62E-04 2.133 2.62E-04 5.58E-04
209.882 2.62E-04 2.138 2.62E-04 5.60E-04
212.020 2.63E-04 2.138 2.63E-04 5.61E-04
214.159 2.67E-04 2.139 2.65E-04 5.67E-04
216.292 2.65E-04 2.133 2.66E-04 5.67E-04
218.425 2.65E-04 2.133 2.65E-04 5.65E-04
220.557 2.65E-04 2.132 2.65E-04 5.65E-04
222.689 2.66E-04 2.132 2.66E-04 5.66E-04
224.821 2.66E-04 2.132 2.66E-04 5.67E-04
226.954 2.66E-04 2.133 2.66E-04 5.67E-04
229.094 2.67E-04 2.14 2.67E-04 5.70E-04
231.226 2.67E-04 2.132 2.67E-04 5.69E-04
233.359 2.66E-04 2.133 2.67E-04 5.68E-04
235.490 2.67E-04 2.131 2.67E-04 5.68E-04
237.622 2.67E-04 2.132 2.67E-04 5.69E-04
239.805 2.68E-04 2.183 2.68E-04 5.84E-04
241.938 2.69E-04 2.133 2.69E-04 5.73E-04
244.072 2.69E-04 2.134 2.69E-04 5.74E-04
246.204 2.70E-04 2.132 2.70E-04 5.75E-04
248.345 2.71E-04 2.141 2.71E-04 5.79E-04
250.478 2.71E-04 2.133 2.71E-04 5.78E-04
252.611 2.72E-04 2.133 2.72E-04 5.79E-04
254.744 2.73E-04 2.133 2.73E-04 5.81E-04
256.876 2.74E-04 2.132 2.74E-04 5.83E-04
259.009 2.75E-04 2.133 2.75E-04 5.86E-04
261.142 2.76E-04 2.133 2.76E-04 5.88E-04
263.274 2.75E-04 2.132 2.76E-04 5.87E-04
265.407 2.76E-04 2.133 2.76E-04 5.88E-04
267.539 2.76E-04 2.132 2.76E-04 5.88E-04
269.670 2.77E-04 2.131 2.77E-04 5.89E-04
271.802 2.77E-04 2.132 2.77E-04 5.91E-04
273.935 2.78E-04 2.133 2.78E-04 5.92E-04
276.355 2.79E-04 2.42 2.79E-04 6.74E-04
278.677 2.79E-04 2.322 2.79E-04 6.48E-04
280.937 2.81E-04 2.26 2.80E-04 6.33E-04
283.072 2.82E-04 2.135 2.82E-04 6.01E-04
285.205 2.83E-04 2.133 2.83E-04 6.03E-04
287.337 2.84E-04 2.132 2.84E-04 6.04E-04
289.470 2.84E-04 2.133 2.84E-04 6.06E-04
291.602 2.85E-04 2.132 2.85E-04 6.07E-04
293.743 2.86E-04 2.141 2.86E-04 6.11E-04
296.031 2.86E-04 2.288 2.86E-04 6.54E-04
298.163 2.87E-04 2.132 2.87E-04 6.11E-04
300.295 2.88E-04 2.132 2.88E-04 6.13E-04
8.05E-02
Where the last value is your desired approximation to the area.
--
Gary's Student
"WayneL" wrote:
> Hi
>
> I have a set of data that needs integrating but it does not fit an easy
> function therefore I need some technique (fuction) like that seen in Flex
> Pro. In this package you select X and Y and press a button titled "Area
> under Curve". This software is expensive and I am sure this could be done
> in Excel.
>
> Cheers
>
> WayneL
>
> P.S
>
> Some example data I am trying to find the Area Under the Curve.
>
>
> Seconds Voltage
> 0.095 4.55E-04
> 2.526 4.14E-04
> 4.659 3.74E-04
> 6.791 3.45E-04
> 8.923 3.25E-04
> 11.055 3.11E-04
> 13.188 3.09E-04
> 15.321 3.05E-04
> 17.454 2.93E-04
> 19.587 2.86E-04
> 21.72 2.80E-04
> 23.855 2.76E-04
> 25.987 2.73E-04
> 28.119 2.70E-04
> 30.301 2.68E-04
> 32.433 2.66E-04
> 34.566 2.64E-04
> 36.698 2.62E-04
> 38.83 2.61E-04
> 41.172 2.60E-04
> 43.305 2.58E-04
> 45.436 2.57E-04
> 47.569 2.57E-04
> 49.702 2.56E-04
> 51.834 2.56E-04
> 53.966 2.56E-04
> 56.1 2.56E-04
> 58.233 2.56E-04
> 60.366 2.56E-04
> 62.499 2.55E-04
> 64.632 2.55E-04
> 66.772 2.55E-04
> 68.954 2.55E-04
> 71.086 2.55E-04
> 73.219 2.53E-04
> 75.357 2.54E-04
> 77.489 2.56E-04
> 79.621 2.55E-04
> 81.753 2.55E-04
> 83.884 2.55E-04
> 86.016 2.55E-04
> 88.149 2.55E-04
> 90.288 2.55E-04
> 92.42 2.55E-04
> 94.552 2.55E-04
> 96.684 2.55E-04
> 98.816 2.55E-04
> 100.949 2.56E-04
> 103.082 2.56E-04
> 105.214 2.56E-04
> 107.347 2.56E-04
> 109.549 2.55E-04
> 111.681 2.55E-04
> 113.813 2.54E-04
> 115.945 2.53E-04
> 118.077 2.53E-04
> 120.208 2.53E-04
> 122.341 2.53E-04
> 124.473 2.53E-04
> 126.605 2.53E-04
> 128.737 2.53E-04
> 130.87 2.53E-04
> 133.003 2.54E-04
> 135.136 2.54E-04
> 137.269 2.55E-04
> 139.401 2.53E-04
> 141.534 2.55E-04
> 143.666 2.57E-04
> 145.798 2.58E-04
> 147.93 2.59E-04
> 150.061 2.58E-04
> 152.193 2.59E-04
> 154.325 2.56E-04
> 156.457 2.57E-04
> 158.589 2.58E-04
> 160.723 2.57E-04
> 162.854 2.58E-04
> 164.986 2.60E-04
> 167.119 2.60E-04
> 169.251 2.61E-04
> 171.384 2.62E-04
> 173.517 2.63E-04
> 175.65 2.64E-04
> 177.785 2.67E-04
> 179.923 2.59E-04
> 182.055 2.59E-04
> 184.188 2.60E-04
> 186.327 2.60E-04
> 188.51 2.61E-04
> 190.649 2.60E-04
> 192.787 2.59E-04
> 194.918 2.60E-04
> 197.059 2.60E-04
> 199.2 2.60E-04
> 201.338 2.60E-04
> 203.476 2.61E-04
> 205.611 2.61E-04
> 207.744 2.62E-04
> 209.882 2.62E-04
> 212.02 2.63E-04
> 214.159 2.67E-04
> 216.292 2.65E-04
> 218.425 2.65E-04
> 220.557 2.65E-04
> 222.689 2.66E-04
> 224.821 2.66E-04
> 226.954 2.66E-04
> 229.094 2.67E-04
> 231.226 2.67E-04
> 233.359 2.66E-04
> 235.49 2.67E-04
> 237.622 2.67E-04
> 239.805 2.68E-04
> 241.938 2.69E-04
> 244.072 2.69E-04
> 246.204 2.70E-04
> 248.345 2.71E-04
> 250.478 2.71E-04
> 252.611 2.72E-04
> 254.744 2.73E-04
> 256.876 2.74E-04
> 259.009 2.75E-04
> 261.142 2.76E-04
> 263.274 2.75E-04
> 265.407 2.76E-04
> 267.539 2.76E-04
> 269.67 2.77E-04
> 271.802 2.77E-04
> 273.935 2.78E-04
> 276.355 2.79E-04
> 278.677 2.79E-04
> 280.937 2.81E-04
> 283.072 2.82E-04
> 285.205 2.83E-04
> 287.337 2.84E-04
> 289.47 2.84E-04
> 291.602 2.85E-04
> 293.743 2.86E-04
> 296.031 2.86E-04
> 298.163 2.87E-04
> 300.295 2.88E-04
>
>
>
> ***********************************************
>
>
>
> "Dana DeLouis" <[email protected]> wrote in message
> news:[email protected]...
> >> ...I want to find the apx area under the curve this data corresponds to.
> >
> > Hi. Not sure if this would help. If you data apr. fits a polynomial
> > curve, then perhaps this may be an alternative.
> > I can't tell from you description, but I assume you have an "x-y scatter
> > chart" ?? Here, I assume your data has range names "x" & "y". Maybe this
> > will give you some ideas to fine what works for your data.
> >
> > Sub Demo()
> > Dim v
> > Dim a, b, c
> > Dim x
> > Dim Area
> >
> > v = [Linest(y,x^{1,2})]
> > a = v(1)
> > b = v(2)
> > c = v(3)
> >
> > x = [Max(x)]
> > Area = (a * x ^ 3) / 3 + (b * x ^ 2) / 2 + c * x
> >
> > x = [Min(x)]
> > Area = Area - ((a * x ^ 3) / 3 + (b * x ^ 2) / 2 + c * x)
> > End Sub
> >
> > HTH :>)
> > --
> > Dana DeLouis
> > Win XP & Office 2003
> >
> >
> > "WayneL" <[email protected]> wrote in message
> > news:[email protected]...
> >> Hi
> >>
> >>
> >> The C1 and C2 corresponds to column 1 & 2. I have x data in column 1
> >> (C1) and y data in C2. The start and end points of the integral in the
> >> beginning and end of the data in both C1 and C2. I should really say I
> >> want to find the apx area under the curve this data corresponds to.
> >>
> >>
> >>
> >> Cheers
> >>
> >> WayneL
> >>
> >> "Harlan Grove" <[email protected]> wrote in message
> >> news:[email protected]...
> >>> "WayneL" <[email protected]> wrote...
> >>>>I have built a spreadsheet that can calculate the area under a curve
> >>>>of a set of data but I would like to have this in VBA for Excel, in
> >>>>say Integral(C1,C2) format or a button on the toolbar.
> >>>>
> >>>>Can anyone point me in the right direction for acquiring the code?
> >>>
> >>> Numerical integration using cmputers isn't a novel concept. What would
> >>> your C1 and C2 be? In other words, what would these cells contain?
> >>> Definite integrals require two end points, but they also require a
> >>> particular curve. How would you specify the particular curve for your
> >>> VBA procedure?
> >>>
> >>
> >>
> >
> >
>
>
>
Gary''s Student wrote...
>Put your data in A1 thru B141
>In C2 put: =A2-A1 the width of the base
>In D2 put: =(B2+B1)/2 the height of the slice
>In E2 put: =C2*D2 the area of the slice
>
>Copy C2,D2,E2 down
>
>In E142 put: =SUM(E2:E141) the sum of the areas
....
Congratulations! This is the trapezoid rule. Why would anyone want to
use so many other cells for intermediate calculations when the same
result can be achieved with a single formula? Indeed, why woud anyone
in their right mind use your column E formulas rather than just
E1:
=SUMPRODUCT(C2:C141,D2:D141)
?
Hi
I have already done this. I need VBA code to conduct it.
Cheers
WayneL
"Gary''s Student" <[email protected]> wrote in message
news:[email protected]...
> Put your data in A1 thru B141
> In C2 put: =A2-A1 the width of the base
> In D2 put: =(B2+B1)/2 the height of the slice
> In E2 put: =C2*D2 the area of the slice
>
> Copy C2,D2,E2 down
>
> In E142 put: =SUM(E2:E141) the sum of the areas
>
> You should see:
>
> 0.095 4.55E-04 base height area
> 2.526 4.14E-04 2.431 4.35E-04 1.06E-03
> 4.659 3.74E-04 2.133 3.94E-04 8.40E-04
> 6.791 3.45E-04 2.132 3.60E-04 7.66E-04
> 8.923 3.25E-04 2.132 3.35E-04 7.14E-04
> 11.055 3.11E-04 2.132 3.18E-04 6.78E-04
> 13.188 3.09E-04 2.133 3.10E-04 6.61E-04
> 15.321 3.05E-04 2.133 3.07E-04 6.55E-04
> 17.454 2.93E-04 2.133 2.99E-04 6.38E-04
> 19.587 2.86E-04 2.133 2.90E-04 6.18E-04
> 21.720 2.80E-04 2.133 2.83E-04 6.04E-04
> 23.855 2.76E-04 2.135 2.78E-04 5.94E-04
> 25.987 2.73E-04 2.132 2.75E-04 5.85E-04
> 28.119 2.70E-04 2.132 2.72E-04 5.79E-04
> 30.301 2.68E-04 2.182 2.69E-04 5.87E-04
> 32.433 2.66E-04 2.132 2.67E-04 5.69E-04
> 34.566 2.64E-04 2.133 2.65E-04 5.65E-04
> 36.698 2.62E-04 2.132 2.63E-04 5.61E-04
> 38.830 2.61E-04 2.132 2.62E-04 5.58E-04
> 41.172 2.60E-04 2.342 2.61E-04 6.10E-04
> 43.305 2.58E-04 2.133 2.59E-04 5.52E-04
> 45.436 2.57E-04 2.131 2.58E-04 5.49E-04
> 47.569 2.57E-04 2.133 2.57E-04 5.48E-04
> 49.702 2.56E-04 2.133 2.57E-04 5.47E-04
> 51.834 2.56E-04 2.132 2.56E-04 5.46E-04
> 53.966 2.56E-04 2.132 2.56E-04 5.46E-04
> 56.100 2.56E-04 2.134 2.56E-04 5.46E-04
> 58.233 2.56E-04 2.133 2.56E-04 5.46E-04
> 60.366 2.56E-04 2.133 2.56E-04 5.46E-04
> 62.499 2.55E-04 2.133 2.56E-04 5.45E-04
> 64.632 2.55E-04 2.133 2.55E-04 5.44E-04
> 66.772 2.55E-04 2.14 2.55E-04 5.46E-04
> 68.954 2.55E-04 2.182 2.55E-04 5.56E-04
> 71.086 2.55E-04 2.132 2.55E-04 5.44E-04
> 73.219 2.53E-04 2.133 2.54E-04 5.42E-04
> 75.357 2.54E-04 2.138 2.54E-04 5.42E-04
> 77.489 2.56E-04 2.132 2.55E-04 5.44E-04
> 79.621 2.55E-04 2.132 2.56E-04 5.45E-04
> 81.753 2.55E-04 2.132 2.55E-04 5.44E-04
> 83.884 2.55E-04 2.131 2.55E-04 5.43E-04
> 86.016 2.55E-04 2.132 2.55E-04 5.44E-04
> 88.149 2.55E-04 2.133 2.55E-04 5.44E-04
> 90.288 2.55E-04 2.139 2.55E-04 5.45E-04
> 92.420 2.55E-04 2.132 2.55E-04 5.44E-04
> 94.552 2.55E-04 2.132 2.55E-04 5.44E-04
> 96.684 2.55E-04 2.132 2.55E-04 5.44E-04
> 98.816 2.55E-04 2.132 2.55E-04 5.44E-04
> 100.949 2.56E-04 2.133 2.56E-04 5.45E-04
> 103.082 2.56E-04 2.133 2.56E-04 5.46E-04
> 105.214 2.56E-04 2.132 2.56E-04 5.46E-04
> 107.347 2.56E-04 2.133 2.56E-04 5.46E-04
> 109.549 2.55E-04 2.202 2.56E-04 5.63E-04
> 111.681 2.55E-04 2.132 2.55E-04 5.44E-04
> 113.813 2.54E-04 2.132 2.55E-04 5.43E-04
> 115.945 2.53E-04 2.132 2.54E-04 5.40E-04
> 118.077 2.53E-04 2.132 2.53E-04 5.39E-04
> 120.208 2.53E-04 2.131 2.53E-04 5.39E-04
> 122.341 2.53E-04 2.133 2.53E-04 5.40E-04
> 124.473 2.53E-04 2.132 2.53E-04 5.39E-04
> 126.605 2.53E-04 2.132 2.53E-04 5.39E-04
> 128.737 2.53E-04 2.132 2.53E-04 5.39E-04
> 130.870 2.53E-04 2.133 2.53E-04 5.40E-04
> 133.003 2.54E-04 2.133 2.54E-04 5.41E-04
> 135.136 2.54E-04 2.133 2.54E-04 5.42E-04
> 137.269 2.55E-04 2.133 2.55E-04 5.43E-04
> 139.401 2.53E-04 2.132 2.54E-04 5.42E-04
> 141.534 2.55E-04 2.133 2.54E-04 5.42E-04
> 143.666 2.57E-04 2.132 2.56E-04 5.46E-04
> 145.798 2.58E-04 2.132 2.58E-04 5.49E-04
> 147.930 2.59E-04 2.132 2.59E-04 5.51E-04
> 150.061 2.58E-04 2.131 2.59E-04 5.51E-04
> 152.193 2.59E-04 2.132 2.59E-04 5.51E-04
> 154.325 2.56E-04 2.132 2.58E-04 5.49E-04
> 156.457 2.57E-04 2.132 2.57E-04 5.47E-04
> 158.589 2.58E-04 2.132 2.58E-04 5.49E-04
> 160.723 2.57E-04 2.134 2.58E-04 5.50E-04
> 162.854 2.58E-04 2.131 2.58E-04 5.49E-04
> 164.986 2.60E-04 2.132 2.59E-04 5.52E-04
> 167.119 2.60E-04 2.133 2.60E-04 5.55E-04
> 169.251 2.61E-04 2.132 2.61E-04 5.55E-04
> 171.384 2.62E-04 2.133 2.62E-04 5.58E-04
> 173.517 2.63E-04 2.133 2.63E-04 5.60E-04
> 175.650 2.64E-04 2.133 2.64E-04 5.62E-04
> 177.785 2.67E-04 2.135 2.66E-04 5.67E-04
> 179.923 2.59E-04 2.138 2.63E-04 5.62E-04
> 182.055 2.59E-04 2.132 2.59E-04 5.52E-04
> 184.188 2.60E-04 2.133 2.60E-04 5.54E-04
> 186.327 2.60E-04 2.139 2.60E-04 5.56E-04
> 188.510 2.61E-04 2.183 2.61E-04 5.69E-04
> 190.649 2.60E-04 2.139 2.61E-04 5.57E-04
> 192.787 2.59E-04 2.138 2.60E-04 5.55E-04
> 194.918 2.60E-04 2.131 2.60E-04 5.53E-04
> 197.059 2.60E-04 2.141 2.60E-04 5.57E-04
> 199.200 2.60E-04 2.141 2.60E-04 5.57E-04
> 201.338 2.60E-04 2.138 2.60E-04 5.56E-04
> 203.476 2.61E-04 2.138 2.61E-04 5.57E-04
> 205.611 2.61E-04 2.135 2.61E-04 5.57E-04
> 207.744 2.62E-04 2.133 2.62E-04 5.58E-04
> 209.882 2.62E-04 2.138 2.62E-04 5.60E-04
> 212.020 2.63E-04 2.138 2.63E-04 5.61E-04
> 214.159 2.67E-04 2.139 2.65E-04 5.67E-04
> 216.292 2.65E-04 2.133 2.66E-04 5.67E-04
> 218.425 2.65E-04 2.133 2.65E-04 5.65E-04
> 220.557 2.65E-04 2.132 2.65E-04 5.65E-04
> 222.689 2.66E-04 2.132 2.66E-04 5.66E-04
> 224.821 2.66E-04 2.132 2.66E-04 5.67E-04
> 226.954 2.66E-04 2.133 2.66E-04 5.67E-04
> 229.094 2.67E-04 2.14 2.67E-04 5.70E-04
> 231.226 2.67E-04 2.132 2.67E-04 5.69E-04
> 233.359 2.66E-04 2.133 2.67E-04 5.68E-04
> 235.490 2.67E-04 2.131 2.67E-04 5.68E-04
> 237.622 2.67E-04 2.132 2.67E-04 5.69E-04
> 239.805 2.68E-04 2.183 2.68E-04 5.84E-04
> 241.938 2.69E-04 2.133 2.69E-04 5.73E-04
> 244.072 2.69E-04 2.134 2.69E-04 5.74E-04
> 246.204 2.70E-04 2.132 2.70E-04 5.75E-04
> 248.345 2.71E-04 2.141 2.71E-04 5.79E-04
> 250.478 2.71E-04 2.133 2.71E-04 5.78E-04
> 252.611 2.72E-04 2.133 2.72E-04 5.79E-04
> 254.744 2.73E-04 2.133 2.73E-04 5.81E-04
> 256.876 2.74E-04 2.132 2.74E-04 5.83E-04
> 259.009 2.75E-04 2.133 2.75E-04 5.86E-04
> 261.142 2.76E-04 2.133 2.76E-04 5.88E-04
> 263.274 2.75E-04 2.132 2.76E-04 5.87E-04
> 265.407 2.76E-04 2.133 2.76E-04 5.88E-04
> 267.539 2.76E-04 2.132 2.76E-04 5.88E-04
> 269.670 2.77E-04 2.131 2.77E-04 5.89E-04
> 271.802 2.77E-04 2.132 2.77E-04 5.91E-04
> 273.935 2.78E-04 2.133 2.78E-04 5.92E-04
> 276.355 2.79E-04 2.42 2.79E-04 6.74E-04
> 278.677 2.79E-04 2.322 2.79E-04 6.48E-04
> 280.937 2.81E-04 2.26 2.80E-04 6.33E-04
> 283.072 2.82E-04 2.135 2.82E-04 6.01E-04
> 285.205 2.83E-04 2.133 2.83E-04 6.03E-04
> 287.337 2.84E-04 2.132 2.84E-04 6.04E-04
> 289.470 2.84E-04 2.133 2.84E-04 6.06E-04
> 291.602 2.85E-04 2.132 2.85E-04 6.07E-04
> 293.743 2.86E-04 2.141 2.86E-04 6.11E-04
> 296.031 2.86E-04 2.288 2.86E-04 6.54E-04
> 298.163 2.87E-04 2.132 2.87E-04 6.11E-04
> 300.295 2.88E-04 2.132 2.88E-04 6.13E-04
> 8.05E-02
> Where the last value is your desired approximation to the area.
>
>
>
> --
> Gary's Student
>
>
> "WayneL" wrote:
>
>> Hi
>>
>> I have a set of data that needs integrating but it does not fit an easy
>> function therefore I need some technique (fuction) like that seen in Flex
>> Pro. In this package you select X and Y and press a button titled "Area
>> under Curve". This software is expensive and I am sure this could be
>> done
>> in Excel.
>>
>> Cheers
>>
>> WayneL
>>
>> P.S
>>
>> Some example data I am trying to find the Area Under the Curve.
>>
>>
>> Seconds Voltage
>> 0.095 4.55E-04
>> 2.526 4.14E-04
>> 4.659 3.74E-04
>> 6.791 3.45E-04
>> 8.923 3.25E-04
>> 11.055 3.11E-04
>> 13.188 3.09E-04
>> 15.321 3.05E-04
>> 17.454 2.93E-04
>> 19.587 2.86E-04
>> 21.72 2.80E-04
>> 23.855 2.76E-04
>> 25.987 2.73E-04
>> 28.119 2.70E-04
>> 30.301 2.68E-04
>> 32.433 2.66E-04
>> 34.566 2.64E-04
>> 36.698 2.62E-04
>> 38.83 2.61E-04
>> 41.172 2.60E-04
>> 43.305 2.58E-04
>> 45.436 2.57E-04
>> 47.569 2.57E-04
>> 49.702 2.56E-04
>> 51.834 2.56E-04
>> 53.966 2.56E-04
>> 56.1 2.56E-04
>> 58.233 2.56E-04
>> 60.366 2.56E-04
>> 62.499 2.55E-04
>> 64.632 2.55E-04
>> 66.772 2.55E-04
>> 68.954 2.55E-04
>> 71.086 2.55E-04
>> 73.219 2.53E-04
>> 75.357 2.54E-04
>> 77.489 2.56E-04
>> 79.621 2.55E-04
>> 81.753 2.55E-04
>> 83.884 2.55E-04
>> 86.016 2.55E-04
>> 88.149 2.55E-04
>> 90.288 2.55E-04
>> 92.42 2.55E-04
>> 94.552 2.55E-04
>> 96.684 2.55E-04
>> 98.816 2.55E-04
>> 100.949 2.56E-04
>> 103.082 2.56E-04
>> 105.214 2.56E-04
>> 107.347 2.56E-04
>> 109.549 2.55E-04
>> 111.681 2.55E-04
>> 113.813 2.54E-04
>> 115.945 2.53E-04
>> 118.077 2.53E-04
>> 120.208 2.53E-04
>> 122.341 2.53E-04
>> 124.473 2.53E-04
>> 126.605 2.53E-04
>> 128.737 2.53E-04
>> 130.87 2.53E-04
>> 133.003 2.54E-04
>> 135.136 2.54E-04
>> 137.269 2.55E-04
>> 139.401 2.53E-04
>> 141.534 2.55E-04
>> 143.666 2.57E-04
>> 145.798 2.58E-04
>> 147.93 2.59E-04
>> 150.061 2.58E-04
>> 152.193 2.59E-04
>> 154.325 2.56E-04
>> 156.457 2.57E-04
>> 158.589 2.58E-04
>> 160.723 2.57E-04
>> 162.854 2.58E-04
>> 164.986 2.60E-04
>> 167.119 2.60E-04
>> 169.251 2.61E-04
>> 171.384 2.62E-04
>> 173.517 2.63E-04
>> 175.65 2.64E-04
>> 177.785 2.67E-04
>> 179.923 2.59E-04
>> 182.055 2.59E-04
>> 184.188 2.60E-04
>> 186.327 2.60E-04
>> 188.51 2.61E-04
>> 190.649 2.60E-04
>> 192.787 2.59E-04
>> 194.918 2.60E-04
>> 197.059 2.60E-04
>> 199.2 2.60E-04
>> 201.338 2.60E-04
>> 203.476 2.61E-04
>> 205.611 2.61E-04
>> 207.744 2.62E-04
>> 209.882 2.62E-04
>> 212.02 2.63E-04
>> 214.159 2.67E-04
>> 216.292 2.65E-04
>> 218.425 2.65E-04
>> 220.557 2.65E-04
>> 222.689 2.66E-04
>> 224.821 2.66E-04
>> 226.954 2.66E-04
>> 229.094 2.67E-04
>> 231.226 2.67E-04
>> 233.359 2.66E-04
>> 235.49 2.67E-04
>> 237.622 2.67E-04
>> 239.805 2.68E-04
>> 241.938 2.69E-04
>> 244.072 2.69E-04
>> 246.204 2.70E-04
>> 248.345 2.71E-04
>> 250.478 2.71E-04
>> 252.611 2.72E-04
>> 254.744 2.73E-04
>> 256.876 2.74E-04
>> 259.009 2.75E-04
>> 261.142 2.76E-04
>> 263.274 2.75E-04
>> 265.407 2.76E-04
>> 267.539 2.76E-04
>> 269.67 2.77E-04
>> 271.802 2.77E-04
>> 273.935 2.78E-04
>> 276.355 2.79E-04
>> 278.677 2.79E-04
>> 280.937 2.81E-04
>> 283.072 2.82E-04
>> 285.205 2.83E-04
>> 287.337 2.84E-04
>> 289.47 2.84E-04
>> 291.602 2.85E-04
>> 293.743 2.86E-04
>> 296.031 2.86E-04
>> 298.163 2.87E-04
>> 300.295 2.88E-04
>>
>>
>>
>> ***********************************************
>>
>>
>>
>> "Dana DeLouis" <[email protected]> wrote in message
>> news:[email protected]...
>> >> ...I want to find the apx area under the curve this data corresponds
>> >> to.
>> >
>> > Hi. Not sure if this would help. If you data apr. fits a polynomial
>> > curve, then perhaps this may be an alternative.
>> > I can't tell from you description, but I assume you have an "x-y
>> > scatter
>> > chart" ?? Here, I assume your data has range names "x" & "y". Maybe
>> > this
>> > will give you some ideas to fine what works for your data.
>> >
>> > Sub Demo()
>> > Dim v
>> > Dim a, b, c
>> > Dim x
>> > Dim Area
>> >
>> > v = [Linest(y,x^{1,2})]
>> > a = v(1)
>> > b = v(2)
>> > c = v(3)
>> >
>> > x = [Max(x)]
>> > Area = (a * x ^ 3) / 3 + (b * x ^ 2) / 2 + c * x
>> >
>> > x = [Min(x)]
>> > Area = Area - ((a * x ^ 3) / 3 + (b * x ^ 2) / 2 + c * x)
>> > End Sub
>> >
>> > HTH :>)
>> > --
>> > Dana DeLouis
>> > Win XP & Office 2003
>> >
>> >
>> > "WayneL" <[email protected]> wrote in message
>> > news:[email protected]...
>> >> Hi
>> >>
>> >>
>> >> The C1 and C2 corresponds to column 1 & 2. I have x data in column 1
>> >> (C1) and y data in C2. The start and end points of the integral in
>> >> the
>> >> beginning and end of the data in both C1 and C2. I should really say
>> >> I
>> >> want to find the apx area under the curve this data corresponds to.
>> >>
>> >>
>> >>
>> >> Cheers
>> >>
>> >> WayneL
>> >>
>> >> "Harlan Grove" <[email protected]> wrote in message
>> >> news:[email protected]...
>> >>> "WayneL" <[email protected]> wrote...
>> >>>>I have built a spreadsheet that can calculate the area under a curve
>> >>>>of a set of data but I would like to have this in VBA for Excel, in
>> >>>>say Integral(C1,C2) format or a button on the toolbar.
>> >>>>
>> >>>>Can anyone point me in the right direction for acquiring the code?
>> >>>
>> >>> Numerical integration using cmputers isn't a novel concept. What
>> >>> would
>> >>> your C1 and C2 be? In other words, what would these cells contain?
>> >>> Definite integrals require two end points, but they also require a
>> >>> particular curve. How would you specify the particular curve for your
>> >>> VBA procedure?
>> >>>
>> >>
>> >>
>> >
>> >
>>
>>
>>
> I have already done this. I need VBA code to conduct it.
(I assume you mean trapezoid) Would something like this work for you?
Here, data was in A1:B10
Sub Apr_Area()
MsgBox "Area: " & [SUMPRODUCT((A2:A10-A1:A9),(B2:B10+B1:B9)/2)]
End Sub
HTH :>)
--
Dana DeLouis
Win XP & Office 2003
"WayneL" <[email protected]> wrote in message
news:[email protected]...
> Hi
>
> I have already done this. I need VBA code to conduct it.
>
> Cheers
>
> WayneL
> "Gary''s Student" <[email protected]> wrote in
> message news:[email protected]...
>> Put your data in A1 thru B141
>> In C2 put: =A2-A1 the width of the base
>> In D2 put: =(B2+B1)/2 the height of the slice
>> In E2 put: =C2*D2 the area of the slice
>>
>> Copy C2,D2,E2 down
>>
>> In E142 put: =SUM(E2:E141) the sum of the areas
>>
>> You should see:
>>
>> 0.095 4.55E-04 base height area
>> 2.526 4.14E-04 2.431 4.35E-04 1.06E-03
>> 4.659 3.74E-04 2.133 3.94E-04 8.40E-04
>> 6.791 3.45E-04 2.132 3.60E-04 7.66E-04
>> 8.923 3.25E-04 2.132 3.35E-04 7.14E-04
>> 11.055 3.11E-04 2.132 3.18E-04 6.78E-04
>> 13.188 3.09E-04 2.133 3.10E-04 6.61E-04
>> 15.321 3.05E-04 2.133 3.07E-04 6.55E-04
>> 17.454 2.93E-04 2.133 2.99E-04 6.38E-04
>> 19.587 2.86E-04 2.133 2.90E-04 6.18E-04
>> 21.720 2.80E-04 2.133 2.83E-04 6.04E-04
>> 23.855 2.76E-04 2.135 2.78E-04 5.94E-04
>> 25.987 2.73E-04 2.132 2.75E-04 5.85E-04
>> 28.119 2.70E-04 2.132 2.72E-04 5.79E-04
>> 30.301 2.68E-04 2.182 2.69E-04 5.87E-04
>> 32.433 2.66E-04 2.132 2.67E-04 5.69E-04
>> 34.566 2.64E-04 2.133 2.65E-04 5.65E-04
>> 36.698 2.62E-04 2.132 2.63E-04 5.61E-04
>> 38.830 2.61E-04 2.132 2.62E-04 5.58E-04
>> 41.172 2.60E-04 2.342 2.61E-04 6.10E-04
>> 43.305 2.58E-04 2.133 2.59E-04 5.52E-04
>> 45.436 2.57E-04 2.131 2.58E-04 5.49E-04
>> 47.569 2.57E-04 2.133 2.57E-04 5.48E-04
>> 49.702 2.56E-04 2.133 2.57E-04 5.47E-04
>> 51.834 2.56E-04 2.132 2.56E-04 5.46E-04
>> 53.966 2.56E-04 2.132 2.56E-04 5.46E-04
>> 56.100 2.56E-04 2.134 2.56E-04 5.46E-04
>> 58.233 2.56E-04 2.133 2.56E-04 5.46E-04
>> 60.366 2.56E-04 2.133 2.56E-04 5.46E-04
>> 62.499 2.55E-04 2.133 2.56E-04 5.45E-04
>> 64.632 2.55E-04 2.133 2.55E-04 5.44E-04
>> 66.772 2.55E-04 2.14 2.55E-04 5.46E-04
>> 68.954 2.55E-04 2.182 2.55E-04 5.56E-04
>> 71.086 2.55E-04 2.132 2.55E-04 5.44E-04
>> 73.219 2.53E-04 2.133 2.54E-04 5.42E-04
>> 75.357 2.54E-04 2.138 2.54E-04 5.42E-04
>> 77.489 2.56E-04 2.132 2.55E-04 5.44E-04
>> 79.621 2.55E-04 2.132 2.56E-04 5.45E-04
>> 81.753 2.55E-04 2.132 2.55E-04 5.44E-04
>> 83.884 2.55E-04 2.131 2.55E-04 5.43E-04
>> 86.016 2.55E-04 2.132 2.55E-04 5.44E-04
>> 88.149 2.55E-04 2.133 2.55E-04 5.44E-04
>> 90.288 2.55E-04 2.139 2.55E-04 5.45E-04
>> 92.420 2.55E-04 2.132 2.55E-04 5.44E-04
>> 94.552 2.55E-04 2.132 2.55E-04 5.44E-04
>> 96.684 2.55E-04 2.132 2.55E-04 5.44E-04
>> 98.816 2.55E-04 2.132 2.55E-04 5.44E-04
>> 100.949 2.56E-04 2.133 2.56E-04 5.45E-04
>> 103.082 2.56E-04 2.133 2.56E-04 5.46E-04
>> 105.214 2.56E-04 2.132 2.56E-04 5.46E-04
>> 107.347 2.56E-04 2.133 2.56E-04 5.46E-04
>> 109.549 2.55E-04 2.202 2.56E-04 5.63E-04
>> 111.681 2.55E-04 2.132 2.55E-04 5.44E-04
>> 113.813 2.54E-04 2.132 2.55E-04 5.43E-04
>> 115.945 2.53E-04 2.132 2.54E-04 5.40E-04
>> 118.077 2.53E-04 2.132 2.53E-04 5.39E-04
>> 120.208 2.53E-04 2.131 2.53E-04 5.39E-04
>> 122.341 2.53E-04 2.133 2.53E-04 5.40E-04
>> 124.473 2.53E-04 2.132 2.53E-04 5.39E-04
>> 126.605 2.53E-04 2.132 2.53E-04 5.39E-04
>> 128.737 2.53E-04 2.132 2.53E-04 5.39E-04
>> 130.870 2.53E-04 2.133 2.53E-04 5.40E-04
>> 133.003 2.54E-04 2.133 2.54E-04 5.41E-04
>> 135.136 2.54E-04 2.133 2.54E-04 5.42E-04
>> 137.269 2.55E-04 2.133 2.55E-04 5.43E-04
>> 139.401 2.53E-04 2.132 2.54E-04 5.42E-04
>> 141.534 2.55E-04 2.133 2.54E-04 5.42E-04
>> 143.666 2.57E-04 2.132 2.56E-04 5.46E-04
>> 145.798 2.58E-04 2.132 2.58E-04 5.49E-04
>> 147.930 2.59E-04 2.132 2.59E-04 5.51E-04
>> 150.061 2.58E-04 2.131 2.59E-04 5.51E-04
>> 152.193 2.59E-04 2.132 2.59E-04 5.51E-04
>> 154.325 2.56E-04 2.132 2.58E-04 5.49E-04
>> 156.457 2.57E-04 2.132 2.57E-04 5.47E-04
>> 158.589 2.58E-04 2.132 2.58E-04 5.49E-04
>> 160.723 2.57E-04 2.134 2.58E-04 5.50E-04
>> 162.854 2.58E-04 2.131 2.58E-04 5.49E-04
>> 164.986 2.60E-04 2.132 2.59E-04 5.52E-04
>> 167.119 2.60E-04 2.133 2.60E-04 5.55E-04
>> 169.251 2.61E-04 2.132 2.61E-04 5.55E-04
>> 171.384 2.62E-04 2.133 2.62E-04 5.58E-04
>> 173.517 2.63E-04 2.133 2.63E-04 5.60E-04
>> 175.650 2.64E-04 2.133 2.64E-04 5.62E-04
>> 177.785 2.67E-04 2.135 2.66E-04 5.67E-04
>> 179.923 2.59E-04 2.138 2.63E-04 5.62E-04
>> 182.055 2.59E-04 2.132 2.59E-04 5.52E-04
>> 184.188 2.60E-04 2.133 2.60E-04 5.54E-04
>> 186.327 2.60E-04 2.139 2.60E-04 5.56E-04
>> 188.510 2.61E-04 2.183 2.61E-04 5.69E-04
>> 190.649 2.60E-04 2.139 2.61E-04 5.57E-04
>> 192.787 2.59E-04 2.138 2.60E-04 5.55E-04
>> 194.918 2.60E-04 2.131 2.60E-04 5.53E-04
>> 197.059 2.60E-04 2.141 2.60E-04 5.57E-04
>> 199.200 2.60E-04 2.141 2.60E-04 5.57E-04
>> 201.338 2.60E-04 2.138 2.60E-04 5.56E-04
>> 203.476 2.61E-04 2.138 2.61E-04 5.57E-04
>> 205.611 2.61E-04 2.135 2.61E-04 5.57E-04
>> 207.744 2.62E-04 2.133 2.62E-04 5.58E-04
>> 209.882 2.62E-04 2.138 2.62E-04 5.60E-04
>> 212.020 2.63E-04 2.138 2.63E-04 5.61E-04
>> 214.159 2.67E-04 2.139 2.65E-04 5.67E-04
>> 216.292 2.65E-04 2.133 2.66E-04 5.67E-04
>> 218.425 2.65E-04 2.133 2.65E-04 5.65E-04
>> 220.557 2.65E-04 2.132 2.65E-04 5.65E-04
>> 222.689 2.66E-04 2.132 2.66E-04 5.66E-04
>> 224.821 2.66E-04 2.132 2.66E-04 5.67E-04
>> 226.954 2.66E-04 2.133 2.66E-04 5.67E-04
>> 229.094 2.67E-04 2.14 2.67E-04 5.70E-04
>> 231.226 2.67E-04 2.132 2.67E-04 5.69E-04
>> 233.359 2.66E-04 2.133 2.67E-04 5.68E-04
>> 235.490 2.67E-04 2.131 2.67E-04 5.68E-04
>> 237.622 2.67E-04 2.132 2.67E-04 5.69E-04
>> 239.805 2.68E-04 2.183 2.68E-04 5.84E-04
>> 241.938 2.69E-04 2.133 2.69E-04 5.73E-04
>> 244.072 2.69E-04 2.134 2.69E-04 5.74E-04
>> 246.204 2.70E-04 2.132 2.70E-04 5.75E-04
>> 248.345 2.71E-04 2.141 2.71E-04 5.79E-04
>> 250.478 2.71E-04 2.133 2.71E-04 5.78E-04
>> 252.611 2.72E-04 2.133 2.72E-04 5.79E-04
>> 254.744 2.73E-04 2.133 2.73E-04 5.81E-04
>> 256.876 2.74E-04 2.132 2.74E-04 5.83E-04
>> 259.009 2.75E-04 2.133 2.75E-04 5.86E-04
>> 261.142 2.76E-04 2.133 2.76E-04 5.88E-04
>> 263.274 2.75E-04 2.132 2.76E-04 5.87E-04
>> 265.407 2.76E-04 2.133 2.76E-04 5.88E-04
>> 267.539 2.76E-04 2.132 2.76E-04 5.88E-04
>> 269.670 2.77E-04 2.131 2.77E-04 5.89E-04
>> 271.802 2.77E-04 2.132 2.77E-04 5.91E-04
>> 273.935 2.78E-04 2.133 2.78E-04 5.92E-04
>> 276.355 2.79E-04 2.42 2.79E-04 6.74E-04
>> 278.677 2.79E-04 2.322 2.79E-04 6.48E-04
>> 280.937 2.81E-04 2.26 2.80E-04 6.33E-04
>> 283.072 2.82E-04 2.135 2.82E-04 6.01E-04
>> 285.205 2.83E-04 2.133 2.83E-04 6.03E-04
>> 287.337 2.84E-04 2.132 2.84E-04 6.04E-04
>> 289.470 2.84E-04 2.133 2.84E-04 6.06E-04
>> 291.602 2.85E-04 2.132 2.85E-04 6.07E-04
>> 293.743 2.86E-04 2.141 2.86E-04 6.11E-04
>> 296.031 2.86E-04 2.288 2.86E-04 6.54E-04
>> 298.163 2.87E-04 2.132 2.87E-04 6.11E-04
>> 300.295 2.88E-04 2.132 2.88E-04 6.13E-04
>> 8.05E-02
>> Where the last value is your desired approximation to the area.
>>
>>
>>
>> --
>> Gary's Student
>>
>>
>> "WayneL" wrote:
>>
>>> Hi
>>>
>>> I have a set of data that needs integrating but it does not fit an easy
>>> function therefore I need some technique (fuction) like that seen in
>>> Flex
>>> Pro. In this package you select X and Y and press a button titled "Area
>>> under Curve". This software is expensive and I am sure this could be
>>> done
>>> in Excel.
>>>
>>> Cheers
>>>
>>> WayneL
>>>
>>> P.S
>>>
>>> Some example data I am trying to find the Area Under the Curve.
>>>
>>>
>>> Seconds Voltage
>>> 0.095 4.55E-04
>>> 2.526 4.14E-04
>>> 4.659 3.74E-04
>>> 6.791 3.45E-04
>>> 8.923 3.25E-04
>>> 11.055 3.11E-04
>>> 13.188 3.09E-04
>>> 15.321 3.05E-04
>>> 17.454 2.93E-04
>>> 19.587 2.86E-04
>>> 21.72 2.80E-04
>>> 23.855 2.76E-04
>>> 25.987 2.73E-04
>>> 28.119 2.70E-04
>>> 30.301 2.68E-04
>>> 32.433 2.66E-04
>>> 34.566 2.64E-04
>>> 36.698 2.62E-04
>>> 38.83 2.61E-04
>>> 41.172 2.60E-04
>>> 43.305 2.58E-04
>>> 45.436 2.57E-04
>>> 47.569 2.57E-04
>>> 49.702 2.56E-04
>>> 51.834 2.56E-04
>>> 53.966 2.56E-04
>>> 56.1 2.56E-04
>>> 58.233 2.56E-04
>>> 60.366 2.56E-04
>>> 62.499 2.55E-04
>>> 64.632 2.55E-04
>>> 66.772 2.55E-04
>>> 68.954 2.55E-04
>>> 71.086 2.55E-04
>>> 73.219 2.53E-04
>>> 75.357 2.54E-04
>>> 77.489 2.56E-04
>>> 79.621 2.55E-04
>>> 81.753 2.55E-04
>>> 83.884 2.55E-04
>>> 86.016 2.55E-04
>>> 88.149 2.55E-04
>>> 90.288 2.55E-04
>>> 92.42 2.55E-04
>>> 94.552 2.55E-04
>>> 96.684 2.55E-04
>>> 98.816 2.55E-04
>>> 100.949 2.56E-04
>>> 103.082 2.56E-04
>>> 105.214 2.56E-04
>>> 107.347 2.56E-04
>>> 109.549 2.55E-04
>>> 111.681 2.55E-04
>>> 113.813 2.54E-04
>>> 115.945 2.53E-04
>>> 118.077 2.53E-04
>>> 120.208 2.53E-04
>>> 122.341 2.53E-04
>>> 124.473 2.53E-04
>>> 126.605 2.53E-04
>>> 128.737 2.53E-04
>>> 130.87 2.53E-04
>>> 133.003 2.54E-04
>>> 135.136 2.54E-04
>>> 137.269 2.55E-04
>>> 139.401 2.53E-04
>>> 141.534 2.55E-04
>>> 143.666 2.57E-04
>>> 145.798 2.58E-04
>>> 147.93 2.59E-04
>>> 150.061 2.58E-04
>>> 152.193 2.59E-04
>>> 154.325 2.56E-04
>>> 156.457 2.57E-04
>>> 158.589 2.58E-04
>>> 160.723 2.57E-04
>>> 162.854 2.58E-04
>>> 164.986 2.60E-04
>>> 167.119 2.60E-04
>>> 169.251 2.61E-04
>>> 171.384 2.62E-04
>>> 173.517 2.63E-04
>>> 175.65 2.64E-04
>>> 177.785 2.67E-04
>>> 179.923 2.59E-04
>>> 182.055 2.59E-04
>>> 184.188 2.60E-04
>>> 186.327 2.60E-04
>>> 188.51 2.61E-04
>>> 190.649 2.60E-04
>>> 192.787 2.59E-04
>>> 194.918 2.60E-04
>>> 197.059 2.60E-04
>>> 199.2 2.60E-04
>>> 201.338 2.60E-04
>>> 203.476 2.61E-04
>>> 205.611 2.61E-04
>>> 207.744 2.62E-04
>>> 209.882 2.62E-04
>>> 212.02 2.63E-04
>>> 214.159 2.67E-04
>>> 216.292 2.65E-04
>>> 218.425 2.65E-04
>>> 220.557 2.65E-04
>>> 222.689 2.66E-04
>>> 224.821 2.66E-04
>>> 226.954 2.66E-04
>>> 229.094 2.67E-04
>>> 231.226 2.67E-04
>>> 233.359 2.66E-04
>>> 235.49 2.67E-04
>>> 237.622 2.67E-04
>>> 239.805 2.68E-04
>>> 241.938 2.69E-04
>>> 244.072 2.69E-04
>>> 246.204 2.70E-04
>>> 248.345 2.71E-04
>>> 250.478 2.71E-04
>>> 252.611 2.72E-04
>>> 254.744 2.73E-04
>>> 256.876 2.74E-04
>>> 259.009 2.75E-04
>>> 261.142 2.76E-04
>>> 263.274 2.75E-04
>>> 265.407 2.76E-04
>>> 267.539 2.76E-04
>>> 269.67 2.77E-04
>>> 271.802 2.77E-04
>>> 273.935 2.78E-04
>>> 276.355 2.79E-04
>>> 278.677 2.79E-04
>>> 280.937 2.81E-04
>>> 283.072 2.82E-04
>>> 285.205 2.83E-04
>>> 287.337 2.84E-04
>>> 289.47 2.84E-04
>>> 291.602 2.85E-04
>>> 293.743 2.86E-04
>>> 296.031 2.86E-04
>>> 298.163 2.87E-04
>>> 300.295 2.88E-04
>>>
>>>
>>>
>>> ***********************************************
>>>
>>>
>>>
>>> "Dana DeLouis" <[email protected]> wrote in message
>>> news:[email protected]...
>>> >> ...I want to find the apx area under the curve this data corresponds
>>> >> to.
>>> >
>>> > Hi. Not sure if this would help. If you data apr. fits a polynomial
>>> > curve, then perhaps this may be an alternative.
>>> > I can't tell from you description, but I assume you have an "x-y
>>> > scatter
>>> > chart" ?? Here, I assume your data has range names "x" & "y". Maybe
>>> > this
>>> > will give you some ideas to fine what works for your data.
>>> >
>>> > Sub Demo()
>>> > Dim v
>>> > Dim a, b, c
>>> > Dim x
>>> > Dim Area
>>> >
>>> > v = [Linest(y,x^{1,2})]
>>> > a = v(1)
>>> > b = v(2)
>>> > c = v(3)
>>> >
>>> > x = [Max(x)]
>>> > Area = (a * x ^ 3) / 3 + (b * x ^ 2) / 2 + c * x
>>> >
>>> > x = [Min(x)]
>>> > Area = Area - ((a * x ^ 3) / 3 + (b * x ^ 2) / 2 + c * x)
>>> > End Sub
>>> >
>>> > HTH :>)
>>> > --
>>> > Dana DeLouis
>>> > Win XP & Office 2003
>>> >
>>> >
>>> > "WayneL" <[email protected]> wrote in message
>>> > news:[email protected]...
>>> >> Hi
>>> >>
>>> >>
>>> >> The C1 and C2 corresponds to column 1 & 2. I have x data in column 1
>>> >> (C1) and y data in C2. The start and end points of the integral in
>>> >> the
>>> >> beginning and end of the data in both C1 and C2. I should really say
>>> >> I
>>> >> want to find the apx area under the curve this data corresponds to.
>>> >>
>>> >>
>>> >>
>>> >> Cheers
>>> >>
>>> >> WayneL
>>> >>
>>> >> "Harlan Grove" <[email protected]> wrote in message
>>> >> news:[email protected]...
>>> >>> "WayneL" <[email protected]> wrote...
>>> >>>>I have built a spreadsheet that can calculate the area under a curve
>>> >>>>of a set of data but I would like to have this in VBA for Excel, in
>>> >>>>say Integral(C1,C2) format or a button on the toolbar.
>>> >>>>
>>> >>>>Can anyone point me in the right direction for acquiring the code?
>>> >>>
>>> >>> Numerical integration using cmputers isn't a novel concept. What
>>> >>> would
>>> >>> your C1 and C2 be? In other words, what would these cells contain?
>>> >>> Definite integrals require two end points, but they also require a
>>> >>> particular curve. How would you specify the particular curve for
>>> >>> your
>>> >>> VBA procedure?
>>> >>>
>>> >>
>>> >>
>>> >
>>> >
>>>
>>>
>>>
>
>
WayneL wrote...
>I have already done this. I need VBA code to conduct it.
....
The interface between Excel and VBA functions used as udfs in Excel
cell formulas is SLOW (in the relative sense, but you'll notice the
effect with only 100 calls or so). Anything that can be done without
VBA is almost always better done without VBA.
Thanks Gregory Vainberg (http://www.vbnumericalmethods.com) for this perfect
solution.
I have tested it with FlexPro and the value correlates.
Cheers
WayneL
Hey Wayne,
I have a number of different methods that you can use, but the easiest
technique is trapezoidal integration. On the website I have a version that
takes the function name as a parameter, but it can be easily adapted to use
2 vectors as parameters as follows:
Public Function TRAPnumint(x, y) As Double
n = Application.Count(x)
TRAPnumint = 0
For t = 2 To n
TRAPnumint = TRAPnumint + 0.5 * (x(t) - x(t - 1)) * (y(t - 1) +
y(t))
Next
End Function
Where x is the column of x values and y is the column of f(x) values.
Hope this helps,
Gregory Vainberg
http://www.vbnumericalmethods.com
"WayneL" <[email protected]> wrote in message
news:[email protected]...
> Hi
>
>
>
> I have built a spreadsheet that can calculate the area under a curve of a
> set of data but I would like to have this in VBA for Excel, in say
> Integral(C1,C2) format or a button on the toolbar.
>
> Can anyone point me in the right direction for acquiring the code?
>
>
>
> Cheers
>
>
>
> WayneL
>
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks