Issue 95135 - Add function for computing linear interpolation of values
Summary: Add function for computing linear interpolation of values
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:
Depends on:
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: ---

Linear interpolation example (13.53 KB, application/vnd.oasis.opendocument.spreadsheet)
2011-03-23 23:00 UTC, sztejkat
no flags Details

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

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, but it's too complicated. Also it's a feature that Excel doesn't
have, but is commonly requested
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"	
		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
		'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's In The

Has anyone seen an extension providing this functionality?

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