“Work and struggle and never accept an evil that you can change.” [André Gide]

Abstract

If you cannot use macros you might be tempted to use the worksheet function TREND for interpolation. Of course you can’t just feed all y-values, x-values (if there are more than 2) and the new x for the unknown y because TREND will just be piecewise accurate.

So you need to identify the surrounding x-values (or the first two resp. the last two in case of extrapolation) for the new x with their corresponding y-values first and then you feed them to TREND.

I don’t like this approach and I won’t recommend to use it but if you do want to, use it correctly:

I deem this approach as inappropriate because you do not really need TREND here - you can easily apply the direct interpolation formula. I think its just too complex and therefore inferior to the (also poor) PERCENTILE / PERCENTRANK approach, despite the fact that it can even extrapolate and that it can cope with non-monotonic y-values.

Use my UDF sbInterp instead, I suggest.

A Sample Application

Please read my Disclaimer.

Interpolation_with_Trend.xlsx [18 KB Excel file, open and use at your own risk]