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.

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.