×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

Area under a curve - in Excel

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

RE: Area under a curve - in Excel

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

RE: Area under a curve - in Excel

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.

RE: Area under a curve - in Excel

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.

RE: Area under a curve - in Excel

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.

RE: Area under a curve - in Excel

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)

RE: Area under a curve - in Excel

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.

RE: Area under a curve - in Excel

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.

RE: Area under a curve - in Excel

(OP)
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

RE: Area under a curve - in Excel

If you go to 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.

RE: Area under a curve - in Excel

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.

RE: Area under a curve - in Excel

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.

RE: Area under a curve - in Excel

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

RE: Area under a curve - in Excel

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.

RE: Area under a curve - in Excel

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources