×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

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!
  • Students Click Here

*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

Jobs

How Do I Get VLOOKUP to Interpolate?

How Do I Get VLOOKUP to Interpolate?

How Do I Get VLOOKUP to Interpolate?

(OP)
I have an array of values and am using the VLOOKUP function to pull the appropriate values from my table so I can run some calculations using those variables.

The problem I'm having is that the values being extracted by VLLOKUP are the *exact* values listed in the array, but I would prefer values that have been linearly interpolated. How can I do this?

I hope my description was clear.

Thanks!

-Christine

RE: How Do I Get VLOOKUP to Interpolate?

Thread770-51434

has several approaches. I found using INDEX and MATCH was the neatest

Cheers

Greg Locock

RE: How Do I Get VLOOKUP to Interpolate?

(OP)
Thanks, Greg. That thread is just what I'm looking for!

I'm very surpised to learn that Excel doesn't have such a function. I was hoping that maybe there was some add-in or something that I missed that would this, but apparently there isn't.

Thanks again,

-Christine

RE: How Do I Get VLOOKUP to Interpolate?

Christine74

I'm not sure what you are working with, but with your sheet can you make several columns and do the vlookup as column 1, column 2 etc.???  This could return several answers for a single problem.  It then depends on how you write the formulas in the array or columns that you are looking up.  

I'm not that good at explaining so I hope this makes since and helps.

David  

RE: How Do I Get VLOOKUP to Interpolate?

I have found the worksheet function "forecast" to be very useful for interpolating.

Gavin.

RE: How Do I Get VLOOKUP to Interpolate?

There are a number of addin fuctions out there you can down load. Try looking up "Excel fuction interpolation" in Goole.

cheers
Kevin

RE: How Do I Get VLOOKUP to Interpolate?

As far as I know forecast cannot be used for interpolation unless the data has an exact linear relationship.

For example with data y=x^2, x= 1 to 10, using forecast at x=4 gives value of 22 instead of 16.

If I am doing anything wrong I would be pleased to know.

Regards
athomas236

RE: How Do I Get VLOOKUP to Interpolate?

Not sure why VLOOKUP is suggested for interpolation I always use the following function for interpolation.

regards,
athomas236

Function Interp(X, Y, Xe)
'   This function interpolates and extrapolates linearly on
'   arrays of X and Y values
'   Input is as follows
'           Array of X values that are in a sequence of increasing values
'           Array of Y values
'           Value Xe for which value of Y is required
    
'   Count number of X values
    For Each Item In X
    CountX = CountX + 1
    Next Item

'   Count number of Y values
    For Each Item In Y
    CountY = CountY + 1
    Next Item

    If CountX >= 2 Then GoTo 10
'   Error not enough values so end calculation
    Msg = "Need at least 2 values of X"
    Ans = MsgBox(Msg, vbOKOnly)
    Interp = "Error"
    GoTo 400
    
10  If CountX = CountY Then GoTo 100
'   Error should be equal number of X and Y values
    Msg = "Need equal number of X & Y values"
    Ans = MsgBox(Msg, vbOKOnly)
    Interp = "Error"
    GoTo 400
    
100 If Xe > X(1) Then GoTo 200
'   Xe less than smallest X
    Interp = Y(2) - ((Y(2) - Y(1)) / (X(2) - X(1)) * (X(2) - Xe))
    GoTo 400

200 If Xe < X(CountX) Then GoTo 300
'   Xe greater than largest X
    Interp = Y(CountX - 1) + ((Y(CountX) - Y(CountX - 1)) / (X(CountX) - X(CountX - 1)) * (Xe - X(CountX - 1)))
    GoTo 400

300 For Item = 1 To CountX
    If Xe >= X(Item) And Xe <= X(Item + 1) Then GoTo 310
    Next Item
310 Interp = (Y(Item + 1) - Y(Item)) / (X(Item + 1) - X(Item)) * (Xe - X(Item)) + Y(Item)

400 '
End Function



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!


Resources