Ask Your Question
1

LOOKUP with linear interpolation for missing values

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

endolith gravatar image

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

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

Comments

The question and all the answers are fascinating. Thank you.

ve3oat gravatar imageve3oat ( 2018-09-25 02:47:46 +0100 )edit

3 Answers

Sort by » oldest newest most voted
2

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

endolith gravatar image

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

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
1

answered 2018-09-24 23:22:36 +0100

Regina gravatar image

updated 2018-09-24 23:40:39 +0100

I want to show you a solution using the widely unknown function MULTIPLE.OPERATIONS. MultipleOperations.ods

It is useful, if you need the same calculation very often. It can be written with one or with two arguments. The example in the attached document uses the version with one argument.

The basic idea is, that you make a template for the calculation. The calculation in the template can be made in several steps. That has the advantage, that the calculation is easier to understand and can be better maintained. It needs neither named expressions nor macros.

After you have made the template calculation, you can use it in the MULTIPLE.OPERATIONS function.

Syntax for one argument MULTIPLE.OPERATIONS(a;b;c)
a contains the cell address of the result cell of the template.
b contains the cell address, which is used for the argument in the template result formula.
c contains the cell address of the current, new value for the argument. A literal value is not possible.

Syntax for two arguments MULTIPLE.OPERATIONS(a;b;c;d;e)
a, b, c same as above
d contains the cell address, which is used for the second argument in the template result formula.
e contains the cell address of the current, new value for the second argument.

edit flag offensive delete link more
0

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

Mike Kaganski gravatar image

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

=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

Comments

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 +0100 )edit

Added an image to clarify

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

Question Tools

2 followers

Stats

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

Seen: 99 times

Last updated: Sep 24