# How do you interpolate data in a horizontal range that's non-linear?

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.

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. 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. Nov 11, 2019 at 11:01
• Is it your intention to create 85+/- separate charts? 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. Nov 11, 2019 at 12:51

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

• Looks like a great start. I could really do with the line of best fit being curved though i.e. with double, triple or more exponential smoothing. That way the final figure wouldn't be the same as the actual number. If possible could you also show your work in a google doc so I can see which cells the formula is drawing from and going to. Nov 11, 2019 at 13:35
• Google Sheet; check the sheet "Interpolate". "I could really do with the line of best fit being curved though". To be honest, I was out of my league doing this much. This kind of maths is not my thing-but it is v. interesting. But I'm learning ;-) Explain double and triple smoothing methods in plain english Nov 11, 2019 at 21:07
• Also Holt-Winters Forecasting for Dummies (or Developers) - Part I, II and III. Always wanted to learn Python... ! Nov 11, 2019 at 21:17
• I've managed to do linear regression: docs.google.com/spreadsheets/d/… But I've been told what I'm hoping to do is called 'spline interpolation'. So as to be able to get viewership figures on a curved line of best fit with multiple peaks and trophs. Nov 17, 2019 at 23:03
• Just found an add-on that offers spline interpolation. sites.google.com/view/interpolation/home Nov 18, 2019 at 3:11