• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
  • Skip to footer
EngineerExcel

EngineerExcel

FREE EBOOK
  • About
  • Excel Course
  • VBA Course
  • Free Training
  • Login

Numerical Integration of Tabular Data in Excel

There are two primary ways to perform numerical integration in Excel:

  • Integration of Tabular Data
  • Integration using VBA

1. Integration of Tabular Data

This type of numerical integration is largely reserved for experimental data.

It is useful for when you want to see how some integral of the experimental data progresses over time.

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

2. Integration using VBA

This method works best when you want to integrate an equation with a larger number of integration points and only want to return a single value.

It can be set up with a user defined function (UDF).

Numerical Integration Methods

Whether you choose to integrate tabular data in the spreadsheet or an equation in VBA, there are two general approximations that are used to estimate the area under the curve.

They are the Midpoint (or Endpoint) Rule and the Trapezoidal rule.

Midpoint Rule

The midpoint rule estimates the area under the curve as a series of pure rectangles (centered on the data point).

As you can imagine, this results in poor accuracy when the integrand is changing rapidly.

It’s best not to use this method if the number of integration points is limited.

Trapezoidal Rule

The trapezoidal rule estimates the area under the curve as a series of trapezoids.

This greatly increases the accuracy, regardless of the change in the integrand.

As the number of integration points increase, the results from these methods will converge.

Example Problem

We are given a table of acceleration data and asked to estimate the velocity and position over time. The acceleration as a function of time is as follows:

To start with, let’s add some columns for Velocity and Position to our data and also fill in the initial values.

We can assume that the object being accelerated here is starting at rest, so it’s velocity and position are “0” at time t=0.

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

Next, we can calculate the velocity. We know that, in general, velocity is related to acceleration by the following equation:

So, to calculate the velocity at any given time, we need to calculate the integral of acceleration through time.

Since we have a finite number of data points the trapezoidal method will give us the greatest accuracy, so let’s use that.

In cell C5 (the first velocity value after the initial velocity, 0, we entered above), enter the formula to calculate the trapezoidal area under the curve.

Filling that formula all the way down gives us the following velocity result:

The velocity result makes sense given the acceleration data. We have a region of progressively increasing velocity from 0-0.1 seconds. Increasing velocity at different rates from 0.1 to ~0.45 seconds and ~0.45 to 0.7 seconds. And constant velocity (zero acceleration) from 0.7 to 1 seconds.

Now we can move on to the position data.

We will enter the same formula for trapezoidal area under the velocity curve to calculate position.

Once again, we fill that equation all the way down to obtain position as a function of time:

Conclusion

This method for performing numerical integration in Excel can be applied to many different problems. Hopefully this post has given you what you need to get started applying this technique to your own work.

[Note: Want to learn even more about advanced Excel techniques? Watch my free training just for engineers. In the three-part video series I'll show you how to easily solve engineering challenges in Excel. Click here to get started.]

Primary Sidebar

Featured Posts

3 Ways to Change the Data in an Excel Scatter Chart

Identify Trends or Out of Range Data in Excel Worksheets

Why Engineers Need to Master Excel

Absolute and Relative Cell References in Engineering Spreadsheets

Colored Vector Plot in Excel

About Me: Charlie Young, P.E.

I’m a licensed professional engineer with a degree in Mechanical Engineering and over a decade of practical experience building engineering applications in Excel. My goal is to help you learn how to turn Excel into a powerful engineering tool.

If you’re interested in learning more, click the button below to receive an update whenever I have a new tip to share. I’ll also send you a copy of my free eBook “10 Smarter Ways to Use Excel for Engineering”.


Subscribe Now

Footer

SOCIAL

Keep up with EngineerExcel:
  • Email
  • RSS
  • YouTube
EngineerExcel

Free Course

Navigation

  • Home
  • About
  • Free Course
  • Excel Tips
  • Resources
  • Coupon
  • Login

Support

  • Support
  • Terms
  • Privacy

Copyright © 2021 EngineerExcel.com · Log in