Linear interpolation

Hi, VBA Experts!

I'd like to ask for help:
I have to code the linear interpretation for missing data.
The way I did is pretty simple, but I got stuck somepoint..

As attached file, I have to find the missing data for the year 4 and 7 from the table(A1:B8) to be able to make the new data array(D1:E10)
I was bale to fill the missing year in the new table, but for the year to be interpolated with Zero value.
I need an advice regarding the logic for finding the next Filled Rate to interpolate with and the logic to be applied for the interpolation formula.

Any help is greatly appreciated.

Code:

---

Function Interpolate(oldRates As Variant) As Variant
Dim i As Integer, j As Integer, oldCount As Integer, Maturity As Integer
' (8,1) in old rates data array highlighted in yellow, so maturity became 10
Maturity = oldRates(oldRates.Rows.Count, 1)
Dim Mat As Variant
ReDim Mat(1 To Maturity, 1 To 2)  ' 1 to 10, 1 to 2:  the new array set up.
'Fills in the years
For i = 1 To Maturity
    Mat(i, 1) = i
    Mat(i, 2) = 0
Next i
'copies over known Rates.
For i = 1 To Maturity          ' 1 to 10: i
  For j = 1 To oldRates.Rows.Count    ' 1 to 8 : J
        If oldRates(j, 1) = Mat(i, 1) Then
            Mat(i, 2) = oldRates(j, 2)
        End If
  Next j
Next i
For i = 1 To Maturity
    If Mat(i, 2) = 0 Then
      'find the next Filled Rate to interpolate with
        For j = i + 1 To Maturity
          'if statement
        Next j
        ' Mat(i,2) = linear interpolation code here.
        Mat(i, 2) = Mat(i - 1, 2) + (Mat(i + 1, 2) - Mat(i - 1, 2)) / (Mat(i + 1, 1) - Mat(i - 1, 1)) * (Mat(i, 1) - Mat(i - 1, 1))
    End If
Next i
Interpolate = Mat
End Function

---

Hello
Have a look at this link (May be helpful for you)
From Here

2 Things

1. I added CODE tags for you -- you can use the [#] icon to insert them and paste your macro(s) between

2. The macro should go on a Standard Module (Insert | Standard Module) not on a worksheet module

Attachment 20420
3. You need to 'array enter' the formula: select the output range of cells and use control+shift+enter.
Don't enter the { } around your formula, the CSE will add that

Attachment 20421


Seems to work, or at least return numbers

(OK, 3 things)


Simple overview of array formulas

https://support.office.com/en-us/art...2-ecfd5caa57c7

Paul,
I was not aware of the code tag rule for uploading the coding part. Thanks for correcting me, also thanks for the useful link for overview of the array formula:)
Yes, it is working for the given data set with missing at t=4 and 7.
But what if we have missing data on t=4 and 5 consecutively, which I attached "VBAExpress2.xls"
It doesn't seem working in that case.

The logic to be used here quite challenging for a VBA newbie like me with double loop and if statement simultaneously.:bug:
I need the general 'if statements' to find the next filled rate to and 'linear interpolation code' as below. Could you please help me?

Code:

---

For i = 1 To Maturity
    If Mat(i, 2) = 0 Then
'find the next Filled Rate to interpolate with
        For j = i + 1 To Maturity
        'if statement
 
        Next j
        ' Mat(i,2) = linear interpolation code here. not sure abt the below.

        Mat(i, 2) = Mat(i - 1, 2) + (Mat(i + 1, 2) - Mat(i - 1, 2)) / (Mat(i + 1, 1) - Mat(i - 1, 1)) * (Mat(i, 1) - Mat(i - 1, 1))
 
    End If

---

I'm new enough to VBA so not sure where i am going wrong, any help is greatly appreciated.

Regards,
Demian

Quote:

---

But what if we have missing data on t=4 and 5 consecutively, which I attached "VBAExpress2.xls"
It doesn't seem working in that case.

---

You have several "Bends" in that data curve, If a "Bend" occurs at a missing data Point, the interpolation will be off

If it's not necessary to 'roll your own' why not use the built in Slope() and Intercept() functions?

They seem to handle your gap case better. I also did a trivial straight line case

Attachment 20428

Code:

---

Option Explicit

Function Interpolate(oldYears As Range, oldRates As Range) As Variant
    Dim i As Long
    Dim A() As Variant
    Dim X As Variant, Y As Variant
    Dim m As Double, b As Double
       
    With Application.WorksheetFunction
        X = .Transpose(oldYears)
        Y = .Transpose(oldRates)
       
        ReDim A(1 To X(UBound(X)), 1 To 2)
       
        m = .Slope(Y, X)
        b = .Intercept(Y, X)
    End With
       
    For i = LBound(A, 1) To UBound(A, 1)
        A(i, 1) = I
        A(i, 2) = m * i + b
    Next i
Interpolate = A
             
End Function

---

I appreciate your sheet. It seems the perfect worksheet for my case.
However, unfortunately, I have to work per the comments here as this is a school project.
At least, there has been some progress in my file, but it still doesn't generate the consecutive missing rates...
Can you take a look at it again?

Code:

---

Option Explicit
Function Interpolate(oldRates As Variant) As Variant
Dim i As Integer, j As Integer, oldCount As Integer, Maturity As Integer
' (8,1) in old rates data array highlighted in yellow, so maturity became 10
Maturity = oldRates(oldRates.Rows.Count, 1)
Dim Mat As Variant
ReDim Mat(1 To Maturity, 1 To 2)  ' 1 to 10, 1 to 2:  the new array set up.
'Fills in the years
For i = 1 To Maturity
    Mat(i, 1) = i
    Mat(i, 2) = 0
Next i
'copies over known Rates.
For i = 1 To Maturity          ' 1 to 10: i
  For j = 1 To oldRates.Rows.Count    ' 1 to 8 : J
        If oldRates(j, 1) = Mat(i, 1) Then
            Mat(i, 2) = oldRates(j, 2)
        End If
  Next j
Next i
For i = 1 To Maturity
    If Mat(i, 2) = 0 Then
        For j = i + 1 To Maturity    'find the next Filled Rate to interpolate with
            If Mat(j, 2) = 0 Then      'if statement
        Next j
        Else
        ' Mat(i,2) = linear interpolation code here
        Mat(i, 2) = Mat(i - 1, 2) + (Mat(j, 2) - Mat(i - 1, 2)) / (Mat(i + 1, 1) - Mat(i - 1, 1)) * (Mat(i, 1) - Mat(i - 1, 1))
    End If
Next i
Interpolate = Mat
End Function

---

I think you were wiping out your data

I added the Exit For to get out of your j loop once the gap is calculated and now everything looks reasonable

(although my math is a little rusty)

Code:

---

Option Explicit

Function Interpolate(oldRates As Variant) As Variant
Dim i As Long, j As Long, Maturity As Integer
' (8,1) in old rates data array highlighted in yellow, so maturity became 10
Maturity = oldRates(oldRates.Rows.Count, 1)
Dim Mat() As Double
ReDim Mat(1 To Maturity, 1 To 2)  ' 1 to 10, 1 to 2:  the new array set up.
'Fills in the years
For i = 1 To Maturity
    Mat(i, 1) = i
    Mat(i, 2) = 0#
Next i
'copies over known Rates.
For i = 1 To oldRates.Rows.Count    ' 1 to 8
  j = oldRates(i, 1).Value
  Mat(j, 2) = oldRates(i, 2)
Next i
For i = 2 To Maturity
    'find if missing
    If Mat(i, 2) = 0# Then
      For j = i + 1 To Maturity    'find the next Filled Rate to interpolate with
          If Mat(j, 2) <> 0 Then      'if statement
            Mat(i, 2) = Mat(i - 1, 2) + (Mat(j, 2) - Mat(i - 1, 2)) / (Mat(j, 1) - Mat(i - 1, 1))
            Exit For    '  <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<
          End If
      Next j
  End If
Next i
Interpolate = Mat
End Function

---

Thank you everyone and Big thanks to Paul!!:hi:
I feel more comfortable with the if stmt now:)
This will get me moving forward on learning VBA.

Stupid question - can you please explain the details of why you need to get out of J loop for (not sure) adding the exit?

Quote:

---

Originally Posted by demian

Stupid question - can you please explain the details of why you need to get out of J loop

---

Once you find the next X(j) with a Y(j) <> 0 and use it to interpolate the X(I) with the Y(I) = 0, you don't want to continue looking since that could overwrite the Y(I) you just calculated