Results 1 to 4 of 4

Thread: Insert Text representing an array into a custom function

  1. #1

    Insert Text representing an array into a custom function



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hey guys,

    I have attached a spreadsheet where the current problem I'm working on involves data found on Columns Y through AK. I have an output table that I need to run some interpolation calculations on. The attached spreadsheet is an exerpt of a larger spreadsheet that is too big to attach to this forum. It contains a module (module1.bas) for an interpolation equation. The function is named interp. The arguement is interp(X range, Y Range, X Value) and returns a Y value at the X value interpolated from the X and Y ranges.

    I now have text representing a range (example text: M227:M263) that has been concatenated from calculations performed to get the correct ranges of data that needs to be interpolated over. I need a way to place this concatenated text into my interp function and get a valid answer (=interp(M227:M263,N227:N263,W5). simply placing the cell containing the text string representing the range does not work. I need this to be dynamic such that cells in columns AI and AJ can autofill and the functions within the cells contain the correct ranges as currently shown in columns AE through AH.

    Any help is appreciated.

    The code for the interp function contained in module1.bas is shown below and will have to be manually added to the spreadsheet:

    Code:
    Function interp(havecol As Range, wantcol As Range, have As Variant)
    'count how many values are in the interpolation range
    k = 1
    Do While (havecol.Cells(k, 1).Value <> 0)
        k = k + 1
    Loop
    k = k - 1
    'find where in the column the value is
    'as soon as reach a value greater, stop--i is the place above
    
    If have < havecol.Cells(k, 1).Value Then
        If have > havecol.Cells(1, 1).Value Then
            i = 1
            Do While (have > havecol.Cells(i, 1).Value)
                i = i + 1
            Loop
            valhi = havecol.Cells(i, 1).Value
            vallo = havecol.Cells(i - 1, 1).Value
            lookhi = wantcol.Cells(i, 1).Value
            looklo = wantcol.Cells(i - 1, 1).Value
            interp = (have - vallo) / (valhi - vallo) * (lookhi - looklo) + looklo
        Else
    
    'extrapolate off the low end...
            If have <> 0 Then
    '            interp = wantcol.Cells(1, 1).Value
                valhi = havecol.Cells(2, 1).Value
                vallo = havecol.Cells(1, 1).Value
                lookhi = wantcol.Cells(2, 1).Value
                looklo = wantcol.Cells(1, 1).Value
                interp = (have - vallo) / (valhi - vallo) * (lookhi - looklo) + looklo
            Else
                interp = 0
            End If
        End If
    Else
    'extrapolate off the high end...
        valhi = havecol.Cells(k, 1).Value
        vallo = havecol.Cells(k - 1, 1).Value
        lookhi = wantcol.Cells(k, 1).Value
        looklo = wantcol.Cells(k - 1, 1).Value
        interp = (have - vallo) / (valhi - vallo) * (lookhi - looklo) + looklo
    End If
    End Function
    Thanks,
    Myles Mc
    Attached Files Attached Files
    Last edited by JoePublic; 2013-10-08 at 10:43 AM. Reason: Add code tags

  2. #2
    Acolyte Andy Pope's Avatar
    Join Date
    Mar 2011
    Location
    London, England
    Posts
    38
    Articles
    0
    You can use the INDIRECT function

    so the formula would look something like,

    =interp(INDIRECT(AE5),INDIRECT(AF5),W5)

    Only problem is you don't seem to creating the range N227:N263. So you can include the OFFSET function to get that range

    =interp(INDIRECT(AE5),OFFSET(INDIRECT(AE5),0,1),W5)
    Cheers
    Andy

    www.andypope.info

  3. #3
    Super Moderator JoePublic's Avatar
    Join Date
    Sep 2011
    Location
    Askew
    Posts
    170
    Articles
    0
    Your formulas are a little confusing as the ranges currently in the formulas don't appear to match the text ranges, but basically you need INDIRECT:
    =interp(INDIRECT(AE5),INDIRECT(AF5),W5)
    for example.
    Circumference of a circle = 2πr²



    ²the circle's radius

  4. #4
    Worked a treat, Thanks!
    I love learning new functions!

    Also, thanks to Joe Public for providing a very similar response.

    Yes, I see that I had an error in my spreadsheet example, good eyes!


    Quote Originally Posted by Andy Pope View Post
    You can use the INDIRECT function

    so the formula would look something like,

    =interp(INDIRECT(AE5),INDIRECT(AF5),W5)

    Only problem is you don't seem to creating the range N227:N263. So you can include the OFFSET function to get that range

    =interp(INDIRECT(AE5),OFFSET(INDIRECT(AE5),0,1),W5)

Posting Permissions

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