PDA

View Full Version : Help Needed Regarding Linear Interpolation and Extrapolation



redsun001
11-03-2017, 02:47 PM
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
11-03-2017, 04:14 PM
Did you just want to do it in VBA or would Trend() suffice?
=TREND(B2:B10,A2:A10,5)

redsun001
11-04-2017, 12:59 PM
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
11-04-2017, 02:40 PM
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