Interpolate

**ryan darrow** · 02-16-2011, 03:24 PM

I need to figure out a way to linearly interpolate a set of values.

I have several sets of data where the independant variables are different and the array size is different. I need to convert all of these data sets into a standard type. For example

I have this data set in columns 1 & 2:
-17.3..... -.4
-15 ........ -.3
-11.6 ..... -.39
-10 ....... -.82
-9 ........ -.76
-8.3 ..... -.71
-7.2 ..... -.65
-6 ...... -.57
..
and I need it to be of the form:

-14 .... X
-13 .... X
-12 .... X
-11 .... X
-10 .. -.82 (some of the values match up, so they need no interpolation)
-9 .... -.76
-8 ...... X

I need to fill in the X's. I guess I need to store the value -14 then search for the gap that it fits in the top table, then do some math gymnastics then spit out the interpolated value. then do the same for -13, -12... and so on. I think I'm trying to make this too difficult because I'm struggling with the implementation of it.

**ryan darrow** · 02-17-2011, 11:42 AM

I figured it out, it's not pretty but it does work.

Sub interpolate()

For i = 1 To 61

a = Cells(i, 6).Value
j = 1
Do
j = j + 1
Loop While a > Cells(j, 1).Value
Cells(i, 3).Value = j

x = Cells(j - 1, 1)
y = Cells(j, 1)
m = Cells(j - 1, 2)
n = Cells(j, 2)

p = n - ((y - a) / (y - x)) * (n - m)

Cells(i, 7) = p
'Cells(1, i + 8) = Cells(i, 7).Text & ","

Next i

End Sub