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:

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

Code 2

Please Login or Register  to view this content.

[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.

Please Login or Register  to view this content.

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.

[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....

[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.