Rubayed Razib Suprov
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.
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:
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.
- 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.
- You can also calculate the step value using the following formula in the cell C18:
=(C16-C15)/(C17+1)
- 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.
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.
- 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.
- 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.
- 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.
- After clicking OK.you will see that both cells D9 and D7 now have the Missing value.
Read More: How to Find Missing Values in Excel (3 Easy Ways)
Similar Readings
- How to Find Missing Rows in Excel (3 Easy Ways)
- Find Missing Values in a List in Excel (3 Easy Methods)
- How to Count Missing Values in Excel (2 Easy Ways)
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:
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.
- Furthermore, select cell E18 and enter the following formula:
=F14/E17
This is the value of the Missing value in cell C9.
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:
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.
- For example, we have a Missing value in cell C9. And the formula span of this cell would be E8:E10.
- 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)
- 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.
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.
