×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Linear Interpolation, one variable, Excel

Linear Interpolation, one variable, Excel

Linear Interpolation, one variable, Excel

(OP)
Further to threads Thread770-14670 and Thread770-13682

A rather verbose, but comprehensive user defined function follows, and can be pasted directly from here, into your spreadsheet.

If anyone has bothered to do one for two variables, I would appreciate a copy.

Thanks Bruce Mutton

Option Explicit

Public Function LInterpolateVLOOKUP(sLookup_value As Single, rTable_array As Range, iCol_index_num As Integer)
'A User Defined Function that returns values linearly interpolated "between"
'the points in the lookup table. May also return error values, hence definition as implied Variant

'Created by Myrna Larson
'obtained from Mr Excel by Bruce Mutton July 2001
'Substantially edited and documented by Bruce Mutton July 2001

    'I have tryed to emulate the behaviour of builtin VLookup function
    'sLookup_value is the value to be found in the first column of the array.
    '               sLookup_value can be a value or a reference
    'rTable_array  is the table of information in which data is looked up.
    '               Use a reference to a range or a range name, such as Database or List.
    '               Values in the first column of table_array must be placed in ASCENDING ORDER:
    '               ..., -2, -1, 0, 1, 2, ... , otherwise LInterpolateVLOOKUP
    '               WILL NOT give the correct value.
    'iCol_index_num    is the column number in rTable_array from which the matching value should be
    '               returned. A iCol_index_num of 1 returns the value in the first column in table_array;
    '               a iCol_index_num of 2 returns the value in the second column in table_array, and so on.
    '               If iCcol_index_num is less than 1, LInterpolateVLOOKUP returns the #VALUE! error value;
    '               if iCol_index_num is greater than the number of columns in table_array, LInterpolateVLOOKUP returns
    '               the #REF! error value.
    '
    'If sLookup_value is smaller than the smallest value, or larger than the largestvalue, in the first
    '   column of rTable_array, LInterpolateVLOOKUP returns the #N/A error value.
    
    Dim iTableRow As Integer
    Dim vTemp As Variant
    Dim dbl_x0 As Double, dbl_x1 As Double, dbl_yo As Double, dbl_y1 As Double
    
    'Deal with obvious user errors
    If iCol_index_num > rTable_array.Columns.Count Then
        LInterpolateVLOOKUP = CVErr(xlErrRef)
        Exit Function
    End If
  
    If sLookup_value < Application.WorksheetFunction.Min(rTable_array.Columns(1)) _
        Or sLookup_value > Application.WorksheetFunction.Max(rTable_array.Columns(1)) Then
            LInterpolateVLOOKUP = CVErr(xlErrNA)
        Exit Function
    End If
    
    'Now the real thing
    On Error Resume Next
    vTemp = Application.WorksheetFunction.Match(sLookup_value, rTable_array.Resize(, 1), 1)
    On Error GoTo 0
    If IsError(vTemp) Then
        LInterpolateVLOOKUP = CVErr(vTemp)
                    
    Else
        iTableRow = CInt(vTemp)
        dbl_x0 = rTable_array(iTableRow, 1)
        dbl_yo = rTable_array(iTableRow, iCol_index_num)
        If sLookup_value = dbl_x0 Then
            LInterpolateVLOOKUP = dbl_yo
        Else
            dbl_x1 = rTable_array(iTableRow + 1, 1)
            dbl_y1 = rTable_array(iTableRow + 1, iCol_index_num)
            LInterpolateVLOOKUP = (sLookup_value - dbl_x0) / (dbl_x1 - dbl_x0) * (dbl_y1 - dbl_yo) + dbl_yo
        End If  'sLookup_value
    End If  'IsError(vTemp)
End Function

RE: Linear Interpolation, one variable, Excel

Ouch!!
Try using a beter tool then Excel like Matlab or TK Solver

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members! Already a Member? Login


Resources

Low-Volume Rapid Injection Molding With 3D Printed Molds
Learn methods and guidelines for using stereolithography (SLA) 3D printed molds in the injection molding process to lower costs and lead time. Discover how this hybrid manufacturing process enables on-demand mold fabrication to quickly produce small batches of thermoplastic parts. Download Now
Design for Additive Manufacturing (DfAM)
Examine how the principles of DfAM upend many of the long-standing rules around manufacturability - allowing engineers and designers to place a part’s function at the center of their design considerations. Download Now
Taking Control of Engineering Documents
This ebook covers tips for creating and managing workflows, security best practices and protection of intellectual property, Cloud vs. on-premise software solutions, CAD file management, compliance, and more. Download Now

Close Box

Join Eng-Tips® Today!

Join your peers on the Internet's largest technical engineering professional community.
It's easy to join and it's free.

Here's Why Members Love Eng-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close