Interpolating missing values in time series

In this video, we demonstrate the application of the Interpolate function in NumXL to find a value for a missing intermediate observation in an evenly spaced time series.

For our demo purposes, we used four different interpolation methods: forward flat, backward flat, linear, and cubic spline.

Video script

Scene 1:

Welcome to part one of the time series data preparation mini tutorial. In this video, we'll use NumXL to interpolate a missing value for an intermediate observation in Excel.

To start, I've already plotted the observations; note that Excel graphs ignore missing values. Let's start with interpolation. We will begin with a forward flat interpolation method.

Using NumXL, type in interpolate in the formula bar and click on the equation editor button.

Scene 2:

The function wizard will pop up. Note how each argument is represented on a separate row and the description displayed. Now select the data range for the X variable.

Next, let's select the Y range or the dependent value. Select the value cell range, don't worry about missing values in the range as the interpolate function will truncate that for you.

Now, let's pick the target or explanatory value for interpolation. Choose the data value in column a, and lock the cell for row movement.

Choose the interpolation method, one for forward flat interpolation. The interpolate function supports four different methods, each method is designated by an inclusive integer value between 1 and 4.

Scene 3:

The function returns div by 0 as the date value is outside the interpolation range, as extrapolation is disabled by default.

Copy the formula to the rest of the column. Notice how only observations with missing values are affected.

Scene 4:

Let's update the graph to plot the forward flat values. Move the column to C. The red mark represents the interpolated point. Note how it is on the same level as the prior observation.

Now, let's do the backward interpolation.

First, let's copy the formula from column C and then edit it to change the interpolation method. Select the copied cell and click the formula toolbar button to launch the function wizard.

Scene 5:

Change the argument value to 2 for backward, flat interpolation, then click OK.

Scene 6:

Copy the value to the rest of the cells in the column.

Once again, update the graph's cell range to reference the backward flat column. Note that the interpolated point in red is now at the same level as the next non-interpolated value.

Now, let's do the linear interpolation. Copy the formula from the first cell in column D to column E.

Scene 7:

Edit the formula, change the interpolation algorithm to three or linear interpolation, and click OK.

Scene 8:

Now copy the formula to the remaining cells in the column. Let's see how they look. Update the input column from D to E. The interpolated point in red is positioned halfway between the surrounding points.

Lastly, let's do the cubic spline interpolation. Copy the formula from column E to column F and edit it in column F.

Scene 9:

Change the interpolation method to four or cubic spline, then click OK.

Scene 10:

Copy the formula in the first cell to the rest of the cells in the same column. Let's update the graph and change the input cell range.

Move the input from column E to column F. The interpolated point in red is between the two points but is slightly different from the linear interpolation.

Scene 11:

That is it for now. Thank you for watching!



Article is closed for comments.

Was this article helpful?
3 out of 4 found this helpful