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!

 

Poorest Man's Interpolation

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

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:

Piecewise_Trend_Example

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.

Sulprobil   Get it done   Contact   Disclaimer   Download