“Everything will be okay in the end. If it’s not okay, then it’s not the end.” [Ed Sheeran]

Abstract

Lorimer Miller came up with a nice and quick but limited linear worksheet function interpolation which works for monotonic functions:

So far everything seems to be ok and you start to think that PERCENTILE and PERCENTRANK offer a nice and attractive way to interpolate linearly.

But:

Now we realize that this approach only works if x values are increasing and y values are decreasing (increasing if you omit “1-” before PERCENTRANK). So this approach is not only not extrapolating, but also not informing if its results are nonsense!

I did not and I still do not like it too much but at least I would like to show how (better: when) it could and - if ever - how it maybe should be applied:

There is also another (piecewise Trend) approach circulating in the web - most often similarly offered without any hint about its prerequisites or limitations.

I recommend to use my UDF sbInterp.