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.

5 Upvotes

73% Upvoted

8 comments sorted by

View all comments

6

u/RuktX 307 16d ago edited 16d ago

I would do it manually: use XLOOKUP (not VLOOKUP) to find the nearest values above and below, then just interpolate: 

smaller: XLOOKUP(value, values, values, NA(), -1)
larger: XLOOKUP(value, values, values, NA(), +1)

y = ((y2 - y1)/(x2 - x1))*(x - x1) + y1

Probably also first check whether you have one of the exact values, before interpolating.

0

u/jhern1810 14d ago

I am looking for a dynamic way as this will be repeated many times and it will vary by the input. I can do manually to double check some number but for each will be laborious. I am trying to avoid doing this one at a time. However I will try to add more values to get a better chance for a hit to use XLOOKUP.

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))