Area under a curve - in Excel
Area under a curve - in Excel
(OP)
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
Basically, I plotted a curve from a range of (X,Y) values. How I want to find the area under the curve.
Speedy





RE: Area under a curve - in Excel
RE: Area under a curve - in Excel
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.
RE: Area under a curve - in Excel
RE: Area under a curve - in Excel
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.
RE: Area under a curve - in Excel
(You can get the trendline equation on the graph using Options)
RE: Area under a curve - in Excel
If you can translate FORTRAN 77 code to VBA, you may find this useful:
h
RE: Area under a curve - in Excel
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.
RE: Area under a curve - in Excel
(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.
RE: Area under a curve - in Excel
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
RE: Area under a curve - in Excel
RE: Area under a curve - in Excel
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.
RE: Area under a curve - in Excel
thread770-64909
thread770-51323
I hope they help.
RE: Area under a curve - in Excel
Good Luck
--------------
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein
RE: Area under a curve - in Excel
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.
RE: Area under a curve - in Excel
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.
RE: Area under a curve - in Excel
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:
which may be of further help or interest.
good luck!
-pmover