Linear Interpolation of a Multidimensional Array

[sperry2565]

I think the title is right...

I've come across many UDF for interpolating when the data is available via the workbook. But I'm trying to populate an interpolated array. I have 9 values already and I need to interpolate the values inbetween them. Essentially if they were on a worksheet it would be:

70	44.66
200	39.57
400	34.58
600	32.99
800	30.05
1000	29.89
1200	29.05
1400	27.63
1600	18.48

Essentially I need to create a "database" that will interpolate each value between 70 and 1600 from the data provided. I'm stuck with the logic...I thought maybe looping through to create the 70 to 1600 using an array created for the other 9 values in the table. But I'm unsure how to call them properly.... Does anyone have any ideas? Thank you

[MrShorty]

It seems like available linear interpolation UDF's should do just fine for this problem. Build another table something like this:

x,y
70,yourlinearinterpolationformula
=A2+1,copy of b2
copy of A3,copy of b2
and so on

You would need to use the right combination of relative and absolute references in the linear interpolation function, but it should do just fine. note that I assumed you want every whole number between 70 and 1600. you could adjust whatever interval you decided to use.

[sperry2565]

So I'm avoiding a sheet entirely. It needs to be done inside the VBA code and not output until prompted to in a specific file. I was just giving an example of what the array would look like if you were to pick it up from the sheet. Otherwise it's hardcoded like Arr = Array(number, number, number). Either way, I ended up reveiwing a bunch of code and putting together this UDF in case someone needs it in the future. Right now it only handles x as a double, I'm trying to get it work with x as an array, or variant, which is also posted below, if anyone knows why I can't get it to show the array in a message box to ensure I actually coded it right can you let me know? (all arrays are the same length obviously) Thanks.

Code 1

Public Function Interp(Temperature As Variant, AllowArr As Variant, x As Double) As Double
If ((x < Temperature(LBound(Temperature))) Or (x > Temperature(UBound(Temperature)))) Then
  MsgBox "Interp: x is out of bound"
  Exit Function
End If

If Temperature(LBound(Temperature)) = x Then
  Interp = AllowArr(LBound(AllowArr))
  Exit Function
End If
Dim i As Single
For i = LBound(Temperature) To UBound(Temperature)
  If Temperature(i) >= x Then
    Interp = AllowArr(i - 1) + (x - Temperature(i - 1)) / (Temperature(i) - Temperature(i - 1)) * (AllowArr(i) - AllowArr(i - 1))
    Exit Function
  End If
Next i
End Function

Code 2

Public Function Interp(Temperature As Variant, AllowArr As Variant, x As Variant) As Double
If ((x(LBound(x)) < Temperature(LBound(Temperature))) Or (x(UBound(x)) > Temperature(UBound(Temperature)))) Then
  MsgBox "Interp: x is out of bound"
  Exit Function
End If

If Temperature(LBound(Temperature)) = x(LBound(x)) Then
  Interp = AllowArr(LBound(AllowArr))
  Exit Function
End If
Dim i As Single
For i = LBound(Temperature) To UBound(Temperature)
  If Temperature(i) >= x(i) Then
    Interp = AllowArr(i - 1) + (x(i - 1) - Temperature(i - 1)) / (Temperature(i) - Temperature(i - 1)) * (AllowArr(i) - AllowArr(i - 1))
    Exit Function
  End If
Next i
End Function

[kalak]

not entirely clear to me just what you wanted, so if only for my own info, here's a VBA code which does some interpolation on your data.
if it's off the mark then a simple "it's off the mark" response would keep me happy.

in the code, a is a variant array, from a worksheet in this example but can come from anywhere, and b is a variant array of interpolated output, which can be put anywhere or just kept in memory.

Sub interpx()
Dim a, b()
Dim mx&, mn&, i&, c&, r&
a = Cells(1).CurrentRegion.Resize(, 2) 'variant array from A1 current region on worksheet, or elsewhere
r = UBound(a, 1)

For i = 1 To r
    If a(i, 1) > mx Then mx = a(i, 1)
    If a(i, 1) < mn Then mn = a(i, 1)
Next i

ReDim b(mx - mn + 1, 1 To 2) 'variant array of interpolated output
For i = 2 To r
    x = a(i, 1): y = a(i - 1, 1)
    For j = 0 To x - y - 1 Step 1 '10
        b(c, 1) = j + y
        b(c, 2) = a(i - 1, 2) + j * (a(i, 2) - a(i - 1, 2)) / (x - y)
        c = c + 1
    Next j
Cells(c + 1, 4).Resize(, 2).Interior.Color = vbCyan
Next i
b(c, 1) = a(r, 1): b(c, 2) = a(r, 2)
Cells(4).Resize(c + 1, 2) = b
Cells(1, 4).Resize(, 2).Interior.Color = vbCyan

End Sub

there's a bit of cell coloring just for fun, but that can be deleted with no loss.

[sperry2565]

Maybe I don't understand....I'm getting a subscript out of range when it enters the for loop.

[kalak]

Maybe I don't understand....I'm getting a subscript out of range when it enters the for loop.

OK. Thanks for the feedback.

It seemed to me you requested doing the operation in computer memory without relying on a worksheet other than for reading in the data and checking the output.

So I copied/pasted your posted data into the range A1:B9 and just ran the code. It ran for me without error.

So I guess I must have interpreted something wrong somewhere.

"Subscript out of range" errors are mostly easy to track down if one knows where they occur.
You didn't say which of the two loops gave that error, but if you feel inclined then just delete the first loop entirely and replace it with mn = 0 and mx = 10^5.

[shg]

How about just a formula?

	A	B	C	D	E	F
1	x	y		x	y
2	70	44.66		70	44.66	E2: =PERCENTILE($B$2:$B$10, 1 - PERCENTRANK($A$2:$A$10, D2))
3	200	39.57		80	44.29
4	400	34.58		90	43.89
5	600	32.99		100	43.52
6	800	30.05		110	43.11
7	1000	29.89		120	42.71
8	1200	29.05		130	42.34
9	1400	27.63		140	41.93
10	1600	18.48		150	41.57
11				160	41.16
12				170	40.75
13				180	40.38
14				190	39.98
15				200	39.57

[sperry2565]

Okay...except it's not going to be done via a worksheet. Otherwise, yes a formula would be a breeze....