Ask Your Question

LOOKUP with linear interpolation for missing values

asked 2018-09-14 15:53:48 +0200

endolith gravatar image

updated 2018-09-14 20:30:15 +0200

I have a table of data like this

0   -105.0
10  -114.3
20  -121.8
30  -127.0
40  -129.6
50  -130.8
60  -131.2

I've discovered the LOOKUP() function, which lets me input a value from the first column and returns a value from the second column. (=LOOKUP(20, A1:A7, B1:B7) returns -121.8, for instance.)

But is there a way to use linear interpolation to fill in the gaps between the samples? For example, a hypothetical =LERPLOOKUP(15, A1:A7, B1:B7) would return -118.07, the midpoint between the 10 and 20 values.

In other words, if the data is the black dots, it should find points along the red lines (linear interpolation), not along the yellow line (linear regression):

image description

(and maybe linearly extrapolate outside the data, though that will be totally wrong for some data, and become increasingly wrong the further out you go.)

edit retag flag offensive close merge delete

2 Answers

Sort by » oldest newest most voted

answered 2018-09-19 17:55:06 +0200

endolith gravatar image

updated 2018-09-19 18:03:51 +0200

I found a solution on an external website: Tarsier Tools Linear Interpolation Function

It uses a syntax very similar to my proposal (=LinearInterpolator($A$1:$A$7, $B$1:$B$7, 15)), and extrapolates outside of bounds, too.

It was a little difficult to get installed, probably because of macro security paranoia, but I put the downloaded file in a trusted folder and was then able to drag it from the document to my default macros folder.

image description

edit flag offensive delete link more

answered 2018-09-14 17:41:51 +0200

Mike Kaganski gravatar image

updated 2018-09-14 17:45:29 +0200

=FORECAST.LINEAR(15;B1:B7;A1:A7) - but it seems to give different result (-116,418) - must have something to do with it specifically targeted at extrapolation

edit flag offensive delete link more


That looks like linear regression, not linear interpolation. So it fits a line to ALL of the points and then interpolates with that, rather than fitting a line to the nearest 2 points and interpolating between them.

endolith gravatar imageendolith ( 2018-09-14 18:06:45 +0200 )edit

Added an image to clarify

endolith gravatar imageendolith ( 2018-09-14 18:16:32 +0200 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-09-14 15:53:48 +0200

Seen: 38 times

Last updated: yesterday