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.
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!
But decide yourself: The original thread where this approach was discussed, is shown here.
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 approach circulating in the web - most often similarly offered without any hint about its prerequisites or limitations.
I recommend to use my UDF Interp.
[Sulprobil] [Get it done] [Organisation] [IT] [Controlling] [HR] [Family] [Contact] [Disclaimer]