I am a supporter of
St. Joseph's hospice.
 If you find this site useful or if it helped you, consider a small donation to
St. Joseph's, please.

Information on
St. Joseph's

JustGiving - Sponsor me now!

 

Poor Man's Interpolation

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

PercentilePercentrank_Example_ok
PercentilePercentrank_Example_ko

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:

PercentilePercentrank_Example_corrected

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

I recommend to use my UDF sbInterp.

Sulprobil   Get it done   Contact   Disclaimer   Download