Ask LibreOffice - RSS feedhttps://ask.libreoffice.org/en/questions/Questions and answers for LibreOfficeenTue, 25 Sep 2018 02:47:46 +0200LOOKUP with linear interpolation for missing valueshttps://ask.libreoffice.org/en/question/165685/lookup-with-linear-interpolation-for-missing-values/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](/upfiles/15369418642278601.png)
(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.)Fri, 14 Sep 2018 15:53:48 +0200https://ask.libreoffice.org/en/question/165685/lookup-with-linear-interpolation-for-missing-values/Comment by ve3oat for <p>I have a table of data like this</p>
<pre><code>0 -105.0
10 -114.3
20 -121.8
30 -127.0
40 -129.6
50 -130.8
60 -131.2
</code></pre>
<p>I've discovered the <code>LOOKUP()</code> function, which lets me input a value from the first column and returns a value from the second column. (<code>=LOOKUP(20, A1:A7, B1:B7)</code> returns -121.8, for instance.)</p>
<p>But is there a way to use linear interpolation to fill in the gaps between the samples? For example, a hypothetical <code>=LERPLOOKUP(15, A1:A7, B1:B7)</code> would return -118.07, the midpoint between the 10 and 20 values. </p>
<p>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):</p>
<p><img alt="image description" src="/upfiles/15369418642278601.png"></p>
<p>(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.)</p>
https://ask.libreoffice.org/en/question/165685/lookup-with-linear-interpolation-for-missing-values/?comment=166655#post-id-166655The question and all the answers are fascinating. Thank you.Tue, 25 Sep 2018 02:47:46 +0200https://ask.libreoffice.org/en/question/165685/lookup-with-linear-interpolation-for-missing-values/?comment=166655#post-id-166655Answer by endolith for <p>I have a table of data like this</p>
<pre><code>0 -105.0
10 -114.3
20 -121.8
30 -127.0
40 -129.6
50 -130.8
60 -131.2
</code></pre>
<p>I've discovered the <code>LOOKUP()</code> function, which lets me input a value from the first column and returns a value from the second column. (<code>=LOOKUP(20, A1:A7, B1:B7)</code> returns -121.8, for instance.)</p>
<p>But is there a way to use linear interpolation to fill in the gaps between the samples? For example, a hypothetical <code>=LERPLOOKUP(15, A1:A7, B1:B7)</code> would return -118.07, the midpoint between the 10 and 20 values. </p>
<p>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):</p>
<p><img alt="image description" src="/upfiles/15369418642278601.png"></p>
<p>(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.)</p>
https://ask.libreoffice.org/en/question/165685/lookup-with-linear-interpolation-for-missing-values/?answer=166143#post-id-166143I found a solution on an external website: [Tarsier Tools Linear Interpolation Function](http://www.tarsiersoft.com/tt/tt03-002/overview.php)
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](/upfiles/15373730281647963.png)Wed, 19 Sep 2018 17:55:06 +0200https://ask.libreoffice.org/en/question/165685/lookup-with-linear-interpolation-for-missing-values/?answer=166143#post-id-166143Answer by Regina for <p>I have a table of data like this</p>
<pre><code>0 -105.0
10 -114.3
20 -121.8
30 -127.0
40 -129.6
50 -130.8
60 -131.2
</code></pre>
<p>I've discovered the <code>LOOKUP()</code> function, which lets me input a value from the first column and returns a value from the second column. (<code>=LOOKUP(20, A1:A7, B1:B7)</code> returns -121.8, for instance.)</p>
<p>But is there a way to use linear interpolation to fill in the gaps between the samples? For example, a hypothetical <code>=LERPLOOKUP(15, A1:A7, B1:B7)</code> would return -118.07, the midpoint between the 10 and 20 values. </p>
<p>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):</p>
<p><img alt="image description" src="/upfiles/15369418642278601.png"></p>
<p>(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.)</p>
https://ask.libreoffice.org/en/question/165685/lookup-with-linear-interpolation-for-missing-values/?answer=166637#post-id-166637I want to show you a solution using the widely unknown function MULTIPLE.OPERATIONS. [MultipleOperations.ods](/upfiles/15378238818123991.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)`<br>
a contains the cell address of the result cell of the template.<br>
b contains the cell address, which is used for the argument in the template result formula.<br>
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)`<br>
a, b, c same as above<br>
d contains the cell address, which is used for the second argument in the template result formula.<br>
e contains the cell address of the current, new value for the second argument.Mon, 24 Sep 2018 23:22:36 +0200https://ask.libreoffice.org/en/question/165685/lookup-with-linear-interpolation-for-missing-values/?answer=166637#post-id-166637Answer by Mike Kaganski for <p>I have a table of data like this</p>
<pre><code>0 -105.0
10 -114.3
20 -121.8
30 -127.0
40 -129.6
50 -130.8
60 -131.2
</code></pre>
<p>I've discovered the <code>LOOKUP()</code> function, which lets me input a value from the first column and returns a value from the second column. (<code>=LOOKUP(20, A1:A7, B1:B7)</code> returns -121.8, for instance.)</p>
<p>But is there a way to use linear interpolation to fill in the gaps between the samples? For example, a hypothetical <code>=LERPLOOKUP(15, A1:A7, B1:B7)</code> would return -118.07, the midpoint between the 10 and 20 values. </p>
<p>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):</p>
<p><img alt="image description" src="/upfiles/15369418642278601.png"></p>
<p>(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.)</p>
https://ask.libreoffice.org/en/question/165685/lookup-with-linear-interpolation-for-missing-values/?answer=165703#post-id-165703`=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 extrapolationFri, 14 Sep 2018 17:41:51 +0200https://ask.libreoffice.org/en/question/165685/lookup-with-linear-interpolation-for-missing-values/?answer=165703#post-id-165703Comment by endolith for <p><code>=FORECAST.LINEAR(15;B1:B7;A1:A7)</code> - but it seems to give different result (-116,418) - must have something to do with it specifically targeted at extrapolation</p>
https://ask.libreoffice.org/en/question/165685/lookup-with-linear-interpolation-for-missing-values/?comment=165707#post-id-165707That 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.Fri, 14 Sep 2018 18:06:45 +0200https://ask.libreoffice.org/en/question/165685/lookup-with-linear-interpolation-for-missing-values/?comment=165707#post-id-165707Comment by endolith for <p><code>=FORECAST.LINEAR(15;B1:B7;A1:A7)</code> - but it seems to give different result (-116,418) - must have something to do with it specifically targeted at extrapolation</p>
https://ask.libreoffice.org/en/question/165685/lookup-with-linear-interpolation-for-missing-values/?comment=165708#post-id-165708Added an image to clarifyFri, 14 Sep 2018 18:16:32 +0200https://ask.libreoffice.org/en/question/165685/lookup-with-linear-interpolation-for-missing-values/?comment=165708#post-id-165708