How to Interpolate Missing Data in Excel (4 Ways)

Get FREE Advanced Excel Exercises with Solutions!

Because of various reasons, we can have datasets with missing data points. It is possible to estimate an approximate value of that data point using many useful interpolation techniques.  If you are curious to know how you can Interpolate Missing data in Excel, then this article may come in handy for you. In this article, we discuss how you can Interpolate Missing data in Excel with elaborate explanations.


Download Practice Workbook

Download this practice workbook below.


4 Easy Ways to Interpolate Missing Data in Excel

We are going to use the below dataset, where we have the product id and those products’ quantities. But at the same time, we also have Missing data points here. By applying various kinds of interpolation methods, we can estimate the approximate value of that Missing cell.

How to Interpolate Missing Data in Excel


1. Use of Linear Trend Method

The linear trend means whether a dataset has variables that follow constant relationship relationships with each other. And at the rate at which the variables change their values almost follows a linear pattern

Formula:

Use of Linear Trend Method to to Interpolate Missing Data in Exce

Here,

(x1, y1) = The First coordinate of the interpolation process.

(x2,y2) = Second point of the interpolation process.

x = Known value.

y =Unknown value.

The dataset that we are going to use has this linear pattern, although it has some of its data Missing, as seen in the image. The Missing data point can be demonstrated by the discontinuities in the line.

If your dataset follows this pattern, then you can use the below method to extract the Missing value.

Steps

  • We have the below dataset where we have the cell data C7 and C9 Missing.
  • Using the surrounding values, we need to estimate the quantity values in those cells.

location of the missing data and the prospective interpolated value.

  • Next, select the range of cells D6:D8, and then go to the Home tab > Editing group.
  • Right after then, go to Fill > Series.

  • In the Series window, click on the columns in the Series option and select Linear in the Type option.
  • You can see that the Step values are now set to 2.5 by default. You can edit these values for your need.

selecting linear type in the series window

  • You can also calculate the step value using the following formula in the cell C18:

=(C16-C15)/(C17+1)

using linear interpolation formula to Interpolate Data that are Missing

  • Repeat the same process for the D9 cell.
  • In order to get the Missing value of the D9 cell, select the range of cells D8:D10, and then go to the Home tab > Editing group.
  • Right after then, go to Fill > Series.
  • In the Series window, click on the columns in the Series option and select Linear in the Type option.
  • You can see that the Step values are now set to 2.5 by default. You can edit these values for your need.
  • Click OK after this.

  • After clicking OK, we can see that the cell with the Missing values now has the values retrieved by using the Linear Trend.

interpolating missing data using the linear trend method

Read More: How to Deal with Missing Data in Excel (6 Suitable Ways)


2. Utilizing Growth Trend Method

When predicting future growth based on past data, growth trend interpolation in Excel can be useful. Linear growth suggests a constant extra increase throughout time, resulting in a straight path on a graph. This means that the annual percentage increase is decreasing slightly with each passing year.

Steps

  • We have the below dataset where we have the cell data C7 and C9 Missing.
  • Using the surrounding values, we need to estimate the quantity values in those cells.

Utilizing Growth Trend Method to to Interpolate Missing Data in Excel

  • Next, select the range of cells D6:D8, and then go to the Home tab > Editing group.
  • Right after then, go to Fill > Series.

  • In the Series window, click on the columns in the Series option and select Growth in the Type option.
  • Tick the Trend check box and click OK.

selection of the growth trend option in the series window

  • Repeat the same process for the second Missing value in cell D9.
  • Next, select the range of cells D8:D10, and then go to the Home tab > Editing group.
  • Right after then, go to Fill > Series.

Using growth series to Interpolate Missing Data in Excel

  • In the Series window, click on the columns in the Series in option and select Growth in the Type option.
  • Tick the Trend check box and click OK.

choosing growth in the type option

  • After clicking OK.you will see that both cells D9 and D7 now have the Missing value.

missing data calculation using the growth trend

Read More: How to Find Missing Values in Excel (3 Easy Ways)


Similar Readings


3. Implementing Weighted Moving Average Formula

Definition:

The weighted moving average (WMA) is a useful metric that gives more weight to the latest data points and less weight to datasets from a long time ago.

Formula:

Implementing Weighted Moving Average Formula to Interpolate Missing Data in Excel

Here,

A1, A2, A3, A4 ….., is the data point of the datasets.

W1, W2, W3, W4…… are the weight assigned to each data point.

Wt is the summation of all weights.

The WMA is calculated by multiplying each number in the data set by a fixed weight and adding the results. Recent data points are given more weight, while previous data points are given less weight. Here, in this method, we will use the SUM function alongside the weighted moving average to estimate the missing value.

Steps

  • Just like before, we have Missing cell values in cells C7 and C9.
  • We will only estimate the Missing value in cell C9.
  • For this method, we need to use the linear values that we calculated before.
  • At the same time, we will put a factor or weight on the cell values. We are going to put 1 for the Missing value for cell C9.
  • And for the immediate cells E8 and E10, we input 0.5.
  • Furthermore, enter 33 in cells D7 and D11, and 0.25 in cells D6 and D12.
  • And finally, enter 0.13 for cells D5 and D13.

  • Select cell F5 and enter the following formula:

=E5*D5

  • Drag the Fill Handle to cell F13.
  • Now the full table is now filled with the multiplication of Weight and Linear values.

  • Select the cell F14 and enter the following formula:

=SUM(F5:F13)

  • Then select cell E17 and enter the following formula:
=SUM(D5:D13)

This will calculate the summation of all of the Weight.

using SUM function to calculate total weight calculation

  • Furthermore, select cell E18 and enter the following formula:

=F14/E17

This is the value of the Missing value in cell C9.

output missing data calculation implementing weighted moving average

Read More: How to Filter Missing Data in Excel (4 Easy Methods)


4. Using Simple Moving Average Formula

Definition:

Simple moving averages compute the average of a price range over a given number of periods.

Formula:

Using Simple Moving Average Formula to Interpolate Missing Data in Excel

Here,

A1, A2, A3, A4……An is the data point of the datasets.

N = Denotes the number of data points.

A simple moving average is a measurement tool that can help predict if an asset price will maintain or if it falls into reverse bull even in a bear trend. Here, in this method, we will use the Average and  IF function alongside the weighted moving average to estimate the missing value.

Steps

  • In the beginning, we need to choose the span range. here we choose span 3.
  • In this method, we have to average the value, which will be done in a spanwise row.
  • For example, we have a Missing value in cell C7. And the formula span of this cell would be E6:E8.

location of the first missing data and range of the simple moving average value

  • For example, we have a Missing value in cell C9. And the formula span of this cell would be E8:E10.

location of the second missing data and simple moving average range

  • We first select cell E5 and enter the following formula:

=IF(C5="",D5,C5)

  • Then repeat the same formula in the cell E6 and enter the following formula:

=IF(C6="",D6,C6)

  • Now, select cell E11 and enter the following formula:

=IF(C11="",D11,C11)

  • Drag the Fill Handle to cell E13.

  • After that, select cell E7 and enter the following formula:

=IF(C7="",AVERAGE(D5:D9),C7)

using combination of IF and AVERAGE function to Interpolate Missing Data in Excel

  • Drag the Fill Handle to cell E10.
  • Doing this will fill the range of cell E8:E10 with a Simple Moving Average Value
  • At the same time, our cell E9 now also has the Missing value presented of cell C9.

output of extracting missing data using the simple moving average

Read More: How to Fill Missing Values in Excel (5 Useful Tricks)


Conclusion

To sum it up, the issue of how we can Interpolate Missing data in Excel is answered here by 4 different examples.

For this problem, a workbook is available to download where you can practice these methods.

Feel free to ask any questions or feedback through the comment section. Any suggestion for the betterment of the Exceldemy community will be highly appreciable.


Related Articles

Rubayed Razib Suprov

Rubayed Razib Suprov

Hi, I am Rubayed Razib Suprov. Graduated from the Bangladesh University of Engineering Technology in Naval Architecture and Marine Engineering department. I joined Exceldemy on March 2022. I have a keen interest in both fluid dynamics and data analysis. I try to teach people what I learned so far about the Excel tool to date, and hope I can make some positive impacts.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo