Issue 95135 - Add function for computing linear interpolation of values
Summary: Add function for computing linear interpolation of values
Status: CLOSED OBSOLETE
Alias: None
Product: Calc
Classification: Application
Component: editing (show other issues)
Version: OOo 3.0
Hardware: All All
: P3 Trivial with 4 votes (vote)
Target Milestone: ---
Assignee: AOO issues mailing list
QA Contact:
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2008-10-18 21:56 UTC by schlotter
Modified: 2017-05-20 09:56 UTC (History)
3 users (show)

See Also:
Issue Type: FEATURE
Latest Confirmation in: ---
Developer Difficulty: ---

Attachments
Linear interpolation example (13.53 KB, application/vnd.oasis.opendocument.spreadsheet)
2011-03-23 23:00 UTC, sztejkat 		no flags Details
Add an attachment (proposed patch, testcase, etc.)

Note You need to log in before you can comment on or make changes to this issue.
Description schlotter 2008-10-18 21:56:02 UTC 
Given a range of x-values and a range of y-values, a function would be useful
that uses these ranges to compute a y-value for a given x-value using linear
interpolation.

Example:
Row/Column      A      B
         1      3      1
         2      5      3
         3      6      8
         4     10      4
         5     12      6

In another cell I would then like to use the function LINEARINTERPOLATION:
=LINEARINTERPOLATION(A1:A5; B1:B5; 4) which would evaluate to 2 because the
linear interpolation from point (3,1) to (5,3) is (4,2). 3 and 5 are the closest
neighbors to 4.

=LINEARINTERPOLATION(A1:A5; B1:B5; 8) would evaluate to 6.

This is a feature that can be created with the current functions of
OpenOffice.org, but it's too complicated. Also it's a feature that Excel doesn't
have, but is commonly requested
(http://www.google.com/search?q=excel+linear+interpolation).
Comment 1 jbf.faure 2008-10-25 11:09:39 UTC 
I confirm it's very hard to design a formula to do linear interpolation. The
best way is to use a function.

Some time ago (OOo 2.x) I wrote such kind of function. Here is my code, you can
use and adapt it (sorry comments are in French) :

function interpoler (X as variant, Y as variant, T as double)
	' on verifie si on a bien des ARRAY
	if NOT (IsArray( X ) AND IsArray( Y )) then
		Err = 519
		interpoler = "#VALUE"	
	else 
		Dim debut as integer, fin as integer, length as Integer
		Dim result As Double
		debut = LBound(X)
		fin = UBound(X)
		length = fin-debut
		Dim X1 as double, X2 as double, Y1 as double, Y2 as double
		
		'on suppose que X est trié par ordre croissant
		If (t < X(debut,1)) then  'avant on prend la première valeur
			interpoler = Y(debut,1)
			'interpoler = "#VALEUR !"
			Exit function
		End if
		If (t >= X(fin,1)) then  'après on garde la dernière valeur
			interpoler = Y(fin,1)
			'interpoler = "#VALEUR !"
			Exit function
		End if
		'X1 = X(debut,1)
		For i = 0 to length-1
			If (X(debut+i,1) <= t AND X(debut+i+1,1) > t) then
				X1 = X(debut+i,1)
				X2 = X(debut+i+1,1)
				Y1 = Y(debut+i,1)
				Y2 = Y(debut+i+1,1)
				Exit For
			End if
		Next
		'interpolation linéaire
		interpoler = y1 + (y2-y1)/(x2-x1)*(t-x1)
	end if
End Function
Comment 2 jarl 2009-04-23 07:34:11 UTC 
I would also like to see this funcitonality in OOo, I give it 2 votes. Further, it would be nice to see
OOo one step ahead of excel when it comes to common requested features.

Meanwhile I found another site describing how to build this piecewise linear function, take a look
at the bottom of
http://www.tutorialsforopenoffice.org/tutorial/LookUp_Functions_In_Calc.html#8.What's In The
Middle_|outline

Has anyone seen an extension providing this functionality?

Jarl
Comment 3 sztejkat 2011-03-23 23:00:31 UTC 
Created attachment 76177 [details]
Linear interpolation example

This file shows how to implement linear interpolation without doing any complex function or complex formulas. Uses LOOKUP and basic math.
Comment 4 Edwin Sharp 2013-12-13 11:13:05 UTC 
Moved to

https://wiki.openoffice.org/wiki/Extensions/Ideas/Calc