Integration/area under a curve VBA code?

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

Harlan
Sun Jan 01 16:07:51 CST 2006

"WayneL" <home@wlawson-nomorespam.co.uk> 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
Sun Jan 01 17:26:03 CST 2006

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" <hrlngrv@aol.com> wrote in message
news:uRChO$xDGHA.3468@TK2MSFTNGP15.phx.gbl...
> "WayneL" <home@wlawson-nomorespam.co.uk> 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?
>

Bucky
Mon Jan 02 00:24:39 CST 2006

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.

Harlan
Mon Jan 02 01:13:07 CST 2006

"WayneL" <home@wlawson-nomorespam.co.uk> 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.

Jerry
Mon Jan 02 06:29:12 CST 2006

Bernard Liengme discusses simple numerical integration at
http://www.stfx.ca/people/bliengme/ExcelTips/AreaUnderCurve.htm
More complex formulas with error bounds are given by
http://www.convertit.com/Go/ConvertIt/Reference/AMS55.ASP?Res=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" <hrlngrv@aol.com> wrote in message
> news:uRChO$xDGHA.3468@TK2MSFTNGP15.phx.gbl...
>
>>"WayneL" <home@wlawson-nomorespam.co.uk> 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?

Dana
Mon Jan 02 06:50:54 CST 2006

> ...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" <home@wlawson-nomorespam.co.uk> wrote in message
news:fyZtf.52788$Cj5.1547@newsfe6-win.ntli.net...
> 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" <hrlngrv@aol.com> wrote in message
> news:uRChO$xDGHA.3468@TK2MSFTNGP15.phx.gbl...
>> "WayneL" <home@wlawson-nomorespam.co.uk> 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
Mon Jan 02 11:15:57 CST 2006

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" <delouis@bellsouth.net> wrote in message
news:Ow7hws5DGHA.1088@tk2msftngp13.phx.gbl...
>> ...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" <home@wlawson-nomorespam.co.uk> wrote in message
> news:fyZtf.52788$Cj5.1547@newsfe6-win.ntli.net...
>> 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" <hrlngrv@aol.com> wrote in message
>> news:uRChO$xDGHA.3468@TK2MSFTNGP15.phx.gbl...
>>> "WayneL" <home@wlawson-nomorespam.co.uk> 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?
>>>
>>
>>
>
>

GarysStudent
Mon Jan 02 11:45:02 CST 2006

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" <delouis@bellsouth.net> wrote in message
> news:Ow7hws5DGHA.1088@tk2msftngp13.phx.gbl...
> >> ...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" <home@wlawson-nomorespam.co.uk> wrote in message
> > news:fyZtf.52788$Cj5.1547@newsfe6-win.ntli.net...
> >> 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" <hrlngrv@aol.com> wrote in message
> >> news:uRChO$xDGHA.3468@TK2MSFTNGP15.phx.gbl...
> >>> "WayneL" <home@wlawson-nomorespam.co.uk> 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?
> >>>
> >>
> >>
> >
> >
>
>
>

Harlan
Mon Jan 02 12:55:55 CST 2006

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)

?

WayneL
Mon Jan 02 17:19:54 CST 2006

Hi

I have already done this. I need VBA code to conduct it.

Cheers

WayneL
"Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in message
news:169B6013-0B84-4D1F-BBAC-68B6639A33AD@microsoft.com...
> 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" <delouis@bellsouth.net> wrote in message
>> news:Ow7hws5DGHA.1088@tk2msftngp13.phx.gbl...
>> >> ...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" <home@wlawson-nomorespam.co.uk> wrote in message
>> > news:fyZtf.52788$Cj5.1547@newsfe6-win.ntli.net...
>> >> 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" <hrlngrv@aol.com> wrote in message
>> >> news:uRChO$xDGHA.3468@TK2MSFTNGP15.phx.gbl...
>> >>> "WayneL" <home@wlawson-nomorespam.co.uk> 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?
>> >>>
>> >>
>> >>
>> >
>> >
>>
>>
>>

Dana
Mon Jan 02 18:50:05 CST 2006

> 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" <home@wlawson-nomorespam.co.uk> wrote in message
news:uyiuf.16304$r4.181@newsfe1-gui.ntli.net...
> Hi
>
> I have already done this. I need VBA code to conduct it.
>
> Cheers
>
> WayneL
> "Gary''s Student" <GarysStudent@discussions.microsoft.com> wrote in
> message news:169B6013-0B84-4D1F-BBAC-68B6639A33AD@microsoft.com...
>> 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" <delouis@bellsouth.net> wrote in message
>>> news:Ow7hws5DGHA.1088@tk2msftngp13.phx.gbl...
>>> >> ...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" <home@wlawson-nomorespam.co.uk> wrote in message
>>> > news:fyZtf.52788$Cj5.1547@newsfe6-win.ntli.net...
>>> >> 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 int