Help Needed Regarding Linear Interpolation and Extrapolation

[redsun001]

Hi all,

I have to do linear interpolation and extrapolation for a given data given below (This data can be extended). I am trying to use an if function to do it but still finding it difficult to understand what conditions to use for if function. There are certain things I need to do... If someone can please help me with the code. It will be hugely appreciated it. Example given below:

A	B
x	y
1	10
2	25
3	50
4	65
6	75
7	85
8	100
9	125
10	150

This is an example data which needs to be interpolated. Let's say I have to find the data point in column B for value of 5 from Column. I realise there are initial steps I need to take to find this.
1. Need to find if the value exist within this range("A") and find its closest larger and smaller number from Column A and B
2. I need to hold those values so that I can interpolate using equation: y = y1 + (x - x1) *(y2-y1)/(x2-x1)

Also, on side note: how do i make this range as an array; should i treat x and y values as different arrays or can I define it as multidimensional array. If latter is correct, how will it be able to find larger and smaller value for x and y from a multidimensional array.

I might be able to figure out extrapolation once I have the code set up for Interpolation. Sorry, if this is too much of trouble. I am still learning how to use vba properly.

/thanks

[Kenneth Hobs]

Did you just want to do it in VBA or would Trend() suffice?
=TREND(B2:B10,A2:A10,5)

[redsun001]

Hi Kenneth, Thanks for your reply. I need to do this in VBA so that I can use it for processing large number of data

Did you just want to do it in VBA or would Trend() suffice?
=TREND(B2:B10,A2:A10,5)

[Kenneth Hobs]

Trend() should handle large data. The way you can use it in VBA depends on how your arrays are setup. Here is one way.

Sub Test_eInterp()  
  '=einterp($B$2:$B$10,$A$2:$A$10,A2)
  MsgBox eInterp([B2:B10], [A2:A10], 5)(1)
End Sub


Function eInterp(Known_ys As Variant, Known_xs As Variant, _
  Optional New_xs As Variant = 0) As Variant
  Dim d As Variant, y() As Variant, x() As Variant
  y() = WorksheetFunction.Transpose(Known_ys)
  x() = WorksheetFunction.Transpose(Known_xs)
  'New_xs = Array(New_xs)
  'd = WorksheetFunction.Trend(y(), x(), New_xs)
  d = WorksheetFunction.Trend(Known_ys, Known_xs, Array(New_xs))
  If d(1) >= Known_ys(1) And d(1) <= Known_ys(2) Then eInterp = d
  eInterp = d
End Function