Area under a curve - in Excel

[Speedy]

Is there a function to calulate the area under a curve in MS Excel.

Basically, I plotted a curve from a range of (X,Y) values. How I want to find the area under the curve.

Speedy

 

[LCruiser]

Delta (Y) at however small an increment you want to wait for. From an engineering perspective, that's good enough.

 

[electricpete]

If your points are unequally spaced, I would say:

the integral series {Ik} would be defined by:

Ik+1 = Ik+ 0.5*(Yk + Yk+1) * (Xk+1 - Xk)

By way of explanation, the quantity 0.5*(Yk + Yk+1) * (Xk+1 - Xk) would be the area of a rectangle bounded horizontally by Xk and Xk+1 and bounded vertically by 0 and the average value of Yk, Yk+1

And you need to pick a value for your first point I0 which amounts to your integration constant (maybe 0?)

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[Dinosaur]

This looks like a basic application of the trapezoidal rule of integration. I have a similar problem with unequally spaced points and a generally parabolically shaped curve. With equal points I could use Simpson's Rule of integration, but I can't with unequal points. If anyone knows how this can be done to about five significant figures of accuracy, I would appreciate the help.

 

[electricpete]

Is it known to be exactly parabolic?

If so, then you have some minumum number of points needed to define the parabola. If more than minimum number of points (problem is overdefined), use a least-squared method to estimate the equation of the parabola (method 1). Then do the integration analytically.

Alternatively, maybe you want to do a spline interpolation (method 2) and integrate the results analytically ?

I would think in general the choice between method 1 and 2 depends on your knowledge of the data. A precise underlying physical knowledge of the model combined with known random error pushes you towards #1 - least-squared fit to the known model. On the other hand if you don't know the exact model but you think your data has no errors in it, you might steer more towards #2.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[StephenA]

If you can find a trendline that fits your curve, then you could integrate that and obtain a calculated value.

(You can get the trendline equation on the graph using Options)

 

[cowski]

...similar problem with unequally spaced points...

If you can translate FORTRAN 77 code to VBA, you may find this useful:

http://www.library.cornell.edu/nr/bookfpdf/f4-5.pdf

 

[electricpete]

That's a cool link. I did find the entire textbook accessible (in individual pieces) at

http://www.library.cornell.edu/nr/

I am curious if there are other Technical Textbooks available for free on-line at that site? (I couldn't find any going through the front page

http://www.library.cornell.edu/)

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[joerd]

These books are a long time classic and a favorite of mine. Sometimes the good things in life are free! An alternative link to the books (both C and Fortran) is

http://library.lanl.gov/numerical/

(via the Los Alamos NL). They have a number of other links to math resources as well.

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.

 

[Speedy]

Guys,

Thanks for all your help.

Integrating the polynominal function of the trendline does seem the best option.

Would be great if MS Excel provided this function though.

Speedy

 

[cowski]

If you go to

http://www.nr.com

they have alternate links to their books (numerical recipes in FORTRAN 77, FORTRAN 90, and C - the C++ version is NOT available as a free download). Also at their site they have links to other sites and their own forum. The page is a little hard on the eyes but it is worth a look.

 

[electricpete]

Thanks cowski and joerd for the links.

About the excel trendline...is that something that let's you see a formula for that excel generates for the best fit line? As far as I can tell it just plots the fit without letting you see the formula (parameters)

If you need to do your own least-squares fit, it is easy to do using the solver tool to minimize the sum of the squares of the residuals.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[cowski]

I don't often use the trendline, but I did a quick search on the term "trendline equation" and found a few threads that may be of interest.

thread770-64909
thread770-51323

I hope they help.

 

[CajunCenturion]

I've never used any of them so I can't speak to their effectiveness, but there are a number of Monte-Carlo simulation add-ins for Excel which might work for you.

Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[electricpete]

Thanks. Reading those threads and playing around with excel, I found that trendline equation can be displayed by right-clicking on the trendline curve, than "format-trendline", then selecting the "options" tab, then checking the box: "Display equation on the chart". Then to get increased precision on the coefficients, right-click on the equation and adjust number format to give desired decimal places.

As far as I can tell there is no easy way to extract those numbers except click on the equation box to edit it and cut/paste it elsewhere in the spreadsheet (then have to dissect the equation to pull out the coefficients). My preference - I would still prefer to do the fit using the solver where I can easily access the results in individual cells.

=====================================
Eng-tips forums: The best place on the web for engineering discussions.

 

[StephenA]

Electricpete.

Yes that is the only way that I have used it (cut pasting the equation), although I have always differentiated the equation to obtain the radius of curvature. It is a bit agricultural but effective.

 

[pmover]

StephenA and electricpete,

using the linest function, one can obtain the coefficients for the trendline of choice, be it polynominial, logarithm, exponential, etc.

see thread:

thread770-23296

or see:

http://www.ozgrid.com/Excel/excel-interpolate-cubic-curve-fit.htm

which may be of further help or interest.

good luck!
-pmover