I've been told what I'm hoping to do is called 'spline interpolation'.

I've imported monthly youtube channel viewership figures from google API. And I've layed out the dates and views in a horizontal range. On the right I want to be able to pick a date and show a rough viewership figure that's close to the actual viewership figure, so interpolating as if it were on a curved line of best fit with multiple peaks and trophs.

The reason is to create a youtube channel directory with monthly actual viewership figure and % monthly difference on a curved line of best fit since the channels beginning.

Here's the google spreadsheet example anyone can edit:


And I need it to work with blank/unknown cells. So using a formula that doesn't treat empty cells as an error, or fills them in based on the average of known figures to both sides in a series.

This is the closest answer I've found so far, but I can't get the formula/code to work:

How to interpolate data in a range in Google Sheets

  • Welcome to WebApps. Would you please explain the outcome that you are trying to achieve and the specific programming problem that you are facing. Your question, as it stands, assumes a level of knowledge about you and your spreadsheet.
    – Tedinoz
    Nov 10, 2019 at 11:44
  • Heya @Tedinoz, thanks for replying, I've updated the post and google doc to try to be more clear. If you have any more questions let me know.
    – WildVirtue
    Nov 11, 2019 at 11:01
  • Is it your intention to create 85+/- separate charts?
    – Tedinoz
    Nov 11, 2019 at 12:37
  • I don't desire to see/create graphs, just use a formula/code to if possible analyse the data and know what a non-linear line of best fit would say was the latest month viewership figure. So a formula drawing from all the numbers in the row and spitting out a number in the final cell under the latest month.
    – WildVirtue
    Nov 11, 2019 at 12:51

1 Answer 1


You are analysing data for 'line of best fit' where there are missing elements in the data range. How to interpolate data in a range in Google Sheets has provided a custom function with which to identify the missing elements.

The following formula attempts to provide a method by which the interpolated data can be calculated.

The formula is a combination of of vlookup to return actual data values and the custom function myInterpolation() to return values for missing data elements.

The data is based on views for channel "Patrik Baboumian"

'=iferror(vlookup(C3,$A$3:$B$26,2,false),round(myInterpolation($A$3:$A$26, $B$3:$B$26, C3),0))

Actual Data and Interpolated data

Actual Data and Interpolated data


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.