Consulting

Results 1 to 10 of 10

Thread: Linear interpolation

  1. #1
    VBAX Regular
    Joined
    Sep 2017
    Posts
    16
    Location

    Red face 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.

    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
    Attached Files Attached Files
    Last edited by Paul_Hossler; 09-21-2017 at 06:33 AM.

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

  3. #3
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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

    Capture.JPG
    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

    Capture2.JPG


    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
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  4. #4
    VBAX Regular
    Joined
    Sep 2017
    Posts
    16
    Location
    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.
    I need the general 'if statements' to find the next filled rate to and 'linear interpolation code' as below. Could you please help me?

    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
    Attached Files Attached Files
    Last edited by demian; 09-21-2017 at 12:15 PM.

  5. #5
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    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
    I expect the student to do their homework and find all the errrors I leeve in.


    Please take the time to read the Forum FAQ

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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

    Capture.JPG


    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
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7
    VBAX Regular
    Joined
    Sep 2017
    Posts
    16
    Location
    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?

    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
    Attached Files Attached Files
    Last edited by Aussiebear; 04-02-2023 at 01:09 PM. Reason: Reduce the whitespace

  8. #8
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    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)

    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
    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  9. #9
    VBAX Regular
    Joined
    Sep 2017
    Posts
    16
    Location
    Thank you everyone and Big thanks to Paul!!
    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?

  10. #10
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by demian View Post

    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
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •