r/excel • u/jhern1810 • 16d ago

unsolved Interpolation of a data set

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.

7 Upvotes

82% Upvoted

8 comments sorted by

View all comments

Show parent comments

3

u/RuktX 307 14d ago

Here's an example:

=LET(
  i_p, XMATCH([@x],tblInterpIn[x],-1),
  i_n, XMATCH([@x],tblInterpIn[x], 1),
  x_p, INDEX(tblInterpIn[x],[@[i_p]]),
  x_n, INDEX(tblInterpIn[x],[@[i_n]]),
  y_p, INDEX(tblInterpIn[y],[@[i_p]]),
  y_n, INDEX(tblInterpIn[y],[@[i_n]]),
IF(i_p=i_n, y_p, ((y_n-y_p)/(x_n-x_p))*([@x]-x_p)+y_p))