Integration/area under a curve VBA code?

W

WayneL

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
 
H

Harlan Grove

WayneL said:
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?
Click to expand...

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?
 
W

WayneL

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
 
B

Bucky

WayneL said:
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.
Click to expand...

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.
 
H

Harlan Grove

WayneL said:
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.
Click to expand...
....

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.
 
D

Dana DeLouis

...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 :>)
 
W

WayneL

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 said:
...I want to find the apx area under the curve this data corresponds to.
Click to expand...

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 said:
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
Click to expand...
Click to expand...
 
G

Guest

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 said:
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 said:
...I want to find the apx area under the curve this data corresponds to.
Click to expand...

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 said:
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

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?
Click to expand...
Click to expand...
Click to expand...
 
H

Harlan Grove

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
Click to expand...
....

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)

?
 
W

WayneL

Hi

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

Cheers

WayneL
Gary''s Student said:
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 said:
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 said:
...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


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

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?
Click to expand...
Click to expand...
Click to expand...
 
D

Dana DeLouis

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 said:
Hi

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

Cheers

WayneL
Gary''s Student said:
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 said:
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



***********************************************



...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


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

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?
Click to expand...
Click to expand...
Click to expand...
 
H

Harlan Grove

WayneL wrote...
I have already done this. I need VBA code to conduct it.
Click to expand...
....

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.
 
W

WayneL

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Integration/area under a curve VBA code? 1
Integration/area under a curve VBA code? 8
Calculation of Area Under a Continuos curve......Scatter Curve. 0
Calculation of Area Under a Continuos curve...... 5
Graphic by curve - extract area 2
Area beneath (or above) curve on graph 2
How to calculate the total area under a curve in excel? 2
How to determine the area under a curve (integration) in Excel? 1

Top