Linear Interpolation

[VB4]

Good Day All!

Would some kind person help me with my first VBA script, linear-interpolation.

After looking at some previous posts I am still finding issues with the boundaries of those scripts.

Problem: There is a sets of data that both vary that I need to complete the gaps for in the columns.

E.g. :



X
Y


1

10


3
14


5
16


7
?


10
22


12
23


15
28


16
30


18
28



Question: How can I create a VBA script to complete a linear interpolation, filling the missing data ?

Any help is much appreciated. :bow:

Thanks,

VB4

P.S. Also a double interpolation would be something I would like to develop further.

[Paul_Hossler]

Maybe something like this.

Needs error checking



Option Explicit


Sub Interp()
Dim r As Range, r1 As Range

Set r = ActiveSheet.Cells(1, 1).CurrentRegion ' input data, X and Y
Set r1 = r.Cells(1, 2).End(xlDown).Offset(1, 0) ' first blank in Y

r1.Value = Application.WorksheetFunction.Forecast_Linear(r1.Offset(0, -1), r.Columns(2), r.Columns(1))


End Sub




29474

[arnelgp]

you can create your own interpolation function:


Public Function interpolate(ByVal Xsml, ByVal Ysml, ByVal Xbig, ByVal Ybig) As Currency
interpolate = Ysml + ((Ybig - Ysml) / (Xbig - Xsml + 1))
End Function


on Cell B5:

=interpolate(A4,B4,A6,B6)

[Paul_Hossler]

Depending on requirement, that may work, but it just comes up with an inside value for 2 points


"Forecast_Linear" uses regression (I believe using Least Squares) to fill in the blank


https://docs.microsoft.com/en-us/office/vba/api/excel.worksheetfunction.forecast_linear?f1url=%3FappId%3DDev11IDEF1%26l%3De n-US%26k%3Dk(vbaxl10.chm137471);k(TargetFrameworkMoniker-Office.Version%3Dv16)%26rd%3Dtrue


Calculates, or predicts, a future value by using existing values. The predicted value is a y-value for a given x-value. The known values are existing x-values and y-values, and the new value is predicted by using linear regression. Use this function to predict future sales, inventory requirements, or consumer trends.

The '2 points' function returns 17, but the regression function returns ~18

OP's choice

29480

29481