Table of Contents
Interpolating missing values in Google Sheets can be done by using the IFERROR function to evaluate the empty cells and interpolate the values between them. This can be done by using the AVERAGE, MAX, MIN, or other similar functions to create a formula that will calculate the missing value based on the values of the cells that are not empty. The interpolated values will then be filled in the empty cells.
Often you may have one or more missing values in a series in Google Sheets that you’d like to fill in.
The following step-by-step example shows how to interpolate missing values in practice.
Step 1: Create the Data
First, let’s create a dataset in Google Sheets that contains some missing values:
Step 2: Calculate the Step Value
Next, we will use the following formula to determine what step value to use to fill in the missing data:
Step = (End – Start) / (#Missing observations + 1)
For this example, we would calculate the step value as:
Step = (35 – 20) / (4 – 1) = 3
We can type the following formula into cell D1 to automatically calculate this value:
=(A13-A8)/(COUNTBLANK(A9:A12)+1)
The following screenshot shows how to use this formula in practice:
Step 3: Interpolate the Missing Values
Next, we need to add the step value to the missing values, starting with the first missing value:
Step 4: Visualize the Interpolated Values
Lastly, we can create a line chart to see if the interpolated values seem to fit the dataset well.
To do so, highlight cells A2:A21, then click the Insert tab, then click Chart.
In the Chart editor panel that appears on the right side of the screen, choose Line chart as the Chart type.
The following line chart will appear:
The interpolated values seem to fit the trend of the dataset well.