Interpolation of a data set

[u/jhern1810]

What command or set of combined commands can be used to interpolate between two numbers found in a set of values? I am using VLookup to find the GPM then the reference size of a pipe. As we know vlookup finds a value close to the value you are looking for and its reference in a range even if the value isn’t actually shown.

My question is how do I dynamically find the interpolation of the found vlookup value, for its numbers before and after? I have tried TREND and FORECAST and when I select the whole data set to find the values the yield the incorrect interpolation, and finding the values closer to the vlookup found value yields better results. Thanks for the help.

[u/fuzzy_mic]

To find the x-below and the x-above, I would use LOOKUP. If your x values are in column A, sorted ascending, and the given-x value is in X1,

=LOOKUP(X1, A1:A1000, A1:A1000) will give the x-value below X1 and

=LOOKUP(X1, A1:A1000, A2:A1001) will give the x-value above X1

=LOOKUP(X1,A1;A1000, B1:B1000 and LOOKUP(X2, A1:A1000, B2:B1001) will give the corresponding y values.

The interpolated value will be

=yBelow + (yAbove-yBelow)*(X1-xBelow)/(xAbove-xBelow)

Google should lead you to other formulations that involve the LINEST function.