How to Interpolate Missing Values in Google Sheets


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:

We can then drag and fill this formula down to each remaining blank cell:

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.

Additional Resource

The following tutorials explain how to perform other common tasks in Google Sheets:

How to Add Multiple Trendlines to Chart in Google Sheets
How to Add a Vertical Line to a Chart in Google Sheets
How to Add Average Line to Chart in Google Sheets

Leave a Reply

Your email address will not be published.