Linear interpolation based on hardcoded data pairs?

I'd like a linear interpolation function that calculates the linear interpolation based on a data set hardcoded within the function itself - rather than data present in a worksheet table.

For instance given the following:

X	Y
1	8
2	5
3	2.5
4	2

The function call =interpolate(1.5) would give a result of 6.5, =interpolate(3.25) would give 2.375.

How would I perform this?

Hi,

You have to deduct the linear formula

Example

y = x^2 + x + 1

Then you write the function

Function interpolar(datapoint As Long)
    interpolar = datapoint ^ 2 + datapoint + 1
End Function

Afterwards you can call the function.

Steffen Thomsen

When I switched from Quattro Pro to Excel, this was one of the things I missed the most -- a built in linear interpolation function.

I think that the hardest part of linear interpolation is extracting the interval containing the unknown. I would probably use the MATCH function. In another part of the spreadsheet

=match(new_x,known_xs)
=match(new_x,known_xs)+1

which will return the location i in the table of the interval. Then

=index(known_xs,i)     =index(known_ys,i)
=index(known_xs,i+1)     =index(know_ys,i+1)

to build a new table containing x1,y1 and x2, y2. Then, you can use either TREND or the point slope formula or whatever to locate new_y

Originally Posted by Steffen Thomsen

Hi,

You have to deduct the linear formula

Example

y = x^2 + x + 1

Sorry, but I am not following? Is this interpolation or something else?

Originally Posted by MrShorty

to build a new table containing x1,y1 and x2, y2.

Thank you, but I am looking for something where the raw data is hardcoded into the function and not passed from a table. I'm not sure what the best way to hardcode the data would be - the only way I'm familiar with is Arrays, but perhaps someone can mention something else. But I'd be thinking something like for instance:

Dim X_vars() As Variant
Dim Y_vars() As Variant
X_vars = Array(1, 2, 3, 4)
Y_vars = Array(8, 5, 2.5, 2)

Question: If I try to use Double as the array type I get a #VALUE! error, instead I have to use variant - why is this?

Here's the interpolation function I've written - feedback and improvements are welcome

Function interpolate(x As Double) As Double
Dim X_vars() As Variant
Dim Y_vars() As Variant
X_vars = Array(1, 2, 3, 4)
Y_vars = Array(8, 5, 2.5, 2)
If x < X_vars(LBound(X_vars)) Or x > X_vars(UBound(X_vars)) Then
    ' Return error if out of bounds
    interpolate = CVErr(xlErrRef)
Else
    Dim index As Integer
    For i = LBound(X_vars) To UBound(X_vars) - 1
        If x >= X_vars(i) And x <= X_vars(i + 1) Then
            index = i
        Exit For
        End If
    Next i
    interpolate = Y_vars(index) + (Y_vars(index + 1) - Y_vars(index)) * (x - X_vars(index)) / (X_vars(index + 1) - X_vars(index))
End If
End Function

How would this work if the data were hardcoded? In your example you would need to pass five arguments, no?

why is this

Because that's how you declare that sort of array! I know that sounds circular but it's just a rule for an array declared in that way. You could make it marginally shorter by removing the loop:

Function interpolate(x As Double) As Double

Dim X_vars() As Variant
Dim Y_vars() As Variant
Dim i As Long
Dim index As Integer

X_vars = Array(1, 2, 3, 4)
Y_vars = Array(8, 5, 2.5, 2)
If x < X_vars(LBound(X_vars)) Or x > X_vars(UBound(X_vars)) Then
    ' Return error if out of bounds
    interpolate = CVErr(xlErrRef)
Else
    index = Application.Match(x, X_vars) - 1
    interpolate = Y_vars(index) + (Y_vars(index + 1) - Y_vars(index)) * (x - X_vars(index)) / (X_vars(index + 1) - X_vars(index))
End If
End Function