×
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

continuous interpolation in Excel
3

continuous interpolation in Excel

continuous interpolation in Excel

(OP)
I am wondering if there is a simpler way to interpolate between a large list of numbers at specified intervals.  For example, I have a large series of data points at 0.6 metre intervals.  I want to interpolate between the points at 0.5 metre intervals.  The problem arises when two data points fall within the same interval.  How do I interpolate without going into the formulas and manually editing them?

RE: continuous interpolation in Excel

Are you talking about linear interpolation?  Even if you are not, why not just come up with the equation of the line that describes your data points.  You can then plug in any x or whatever and get your desired results at any point?  

I sometimes plot the data on a chart, find the best trend line that models the data, have excel display the equation on the chart, and then use that equation for interpolation.

RE: continuous interpolation in Excel

3
I recently faced a similar problem.  Here's the problem and how I solved it:

I had about 1620 cylinder pressure vs crank angle data points from a client, where crank angle was not given in steady increments, but was always increasing.  Sometimes the angle would go up by 0.1 degrees, sometimes by 0.73, etc.  I had to convert this information into 500 (or less) evenly spaced data points representing cyl pressure over 720 degrees of crankshaft rotation.  I wrote a macro that does the following:

1) Receive as input the complete list of data (x and y), and store it in an array

2) receive as input the X value for which a Y value is desired

3) loop through the list of data to find two input X locations that bracket the desired X location

4) interpolate linearly between those two locations to get the Y value at the new location

5) output the Y value corresponding to that X location.

Then calculating the evenly spaced values was a simple matter of calling (from the spreadsheet) my function, with an input range giving known X and Y values, and a single cell containing the desired X value.  The output was the desired Y value.  The only tricky part was that the end of my input data was supposed to be the same as the beginning, so I had to tailor my code to allow the desired point to fall between the last point and the first one.  Another note would be that you should make sure your data is in order (sorted) before running, or set up your code to sort the input values before further processing.

RE: continuous interpolation in Excel

I have had great success with linear interpolation, using the data offset vertically by one cell, in effect, and then using VLOOKUP to test for a higher and lower limit for each desired interpolation point.

Unfortunately the dog ate my homework (well, they've replaced this computer) so I don't have it with me, but I think that should be enough to get an adept Excel user going!

Cheers

Greg Locock

RE: continuous interpolation in Excel

Try this code in a module, it will allow you to Interpolate between points, quite handy.


Function Interpolation(X1, Y1, X2, Y2, X3)

Dim dX, dY, dYdX, Y3

dX = X2 - X1
dY = Y2 - Y1
dYdX = dY / dX
Interpolation = (((X3 - X1) * dYdX) + Y1)

End Function

RE: continuous interpolation in Excel

Personally, I would prefer to write VBA function to do linerar interpolation, but there's a way to use just spereadsheet formulas as well.

Name the range containing locations of data points as X (has to be in descending or ascending order, single column or single row) and the range of corresponding values as Y.

Cell A1 - input value for X-location
Cell B1 - interpolated value of Y
Cell C1 - index value (used to shorten interpolation formula)

Cell C1 formula checks the order of X-table and finds the index of closest to input value:

=MATCH(A1,X,IF(ISNA(LOOKUP(A1,X)),-1,1))

Cell B1 formula performs linear interpolation:

=INDEX(Y,C1)+(A1-INDEX(X,C1))/(INDEX(X,C1+1)-INDEX(X,C1))*(INDEX(Y,C1+1)-INDEX(Y,C1))

You can create a table copying cells A1:C1 down.
Hope it will work for you. Email me if you having problems and I'll send you a file.

http://yakpol.net

RE: continuous interpolation in Excel

Karmond,  

That function will allow you to interpolate if you already know what x1, x2, y1, and y2 are.  The original question (I think) was referring to a situation where x1, x2, y1, and y2 are in a list of values, and need to be identified automatically for a given x3.

Greg,  

Thanks for the info on Vlookup. That's a very useful worksheet function.  I can't figure out how to make it cough up the value one step larger than the search value though (it defaults to the next smaller value).  Thanks for showing me that one though, I'll definitely be using it in the future.

Here's another solution that I came up with (inspired by Greg's post):

if we have a spreadsheet where
* column A contains known x values
* column B contains known Y values
* col D contains X values for which a Y value is desired
* values in column x are in ascending order
* cols A, B, and D have data in rows 2 thru 555

then:

=match(D2,$A$2:$A$555,1) gives the row number within 2-555 where the X value just lower than the X value in D2 is located  (we'll store this value in E2)

=INDEX($A$2:$A$555,E2,1) gives the value of "lower X" (we'll store it in F2)

=INDEX($A$2:$A$555,E2+1,1) gives the value of "upper X" (we'll store it in G2)

=INDEX($B$2:$B$555,E2,1) gives the value of "lower Y" (we'll store it in H2)

=INDEX($B$2:$B$555,E2+1,1) gives the value of "upper Y" (we'll store it in I2)

=(D2-F2)/(G2-F2)*(I2-H2)+H2 gives us the value we are looking for.

alternatively, we could condense all of those equations into one bulky one:

=(D2-INDEX($A$2:$A$555,match(D2,$A$2:$A$555,1),1))/(G2-INDEX($A$2:$A$555,match(D2,$A$2:$A$555,1),1))*(INDEX($B$2:$B$555,match(D2,$A$2:$A$555,1)+1,1)-INDEX($B$2:$B$555,match(D2,$A$2:$A$555,1),1))+INDEX($B$2:$B$555,match(D2,$A$2:$A$555,1),1)

whew, that's a mouthful.



RE: continuous interpolation in Excel

yakpol,

looks like we were typing our solutions at the same time - I was wondering why I didn't see yours there before I posted.

there's a typo in mine that I felt obligated to correct (I didn't substitute the formula in for G2 in one spot)

the correct messy formula is (I believe)

=(D2-INDEX($A$2:$A$555,match(D2,$A$2:$A$555,1),1))/(INDEX($A$2:$A$555,E2+1,1)-INDEX($A$2:$A$555,match(D2,$A$2:$A$555,1),1))*(INDEX($B$2:$B$555,match(D2,$A$2:$A$555,1)+1,1)-INDEX($B$2:$B$555,match(D2,$A$2:$A$555,1),1))+INDEX($B$2:$B$555,match(D2,$A$2:$A$555,1),1)

RE: continuous interpolation in Excel

ivymike,
We, probably, posted the solutions in the same time. I tried to accomodate ascending and descending order of data points. The functions MATCH() and LOOKUP() work inconsistently with descending data, more bulletproof solution to my previous post:

Cell C1 formula: =MATCH(A1,X,IF(ISNA(MATCH(A1,X,-1)),1,-1))

RE: continuous interpolation in Excel

nope, I missed an E2 as well.  I give up.

RE: continuous interpolation in Excel

hey, that isna is pretty neat.  I really learned a lot today.

RE: continuous interpolation in Excel

I used the messy formula
=(D2- INDEX($A$2:$A$57,E2,1))/( INDEX($A$2:$A$57,E2+1,1)- INDEX($A$2:$A$57,E2,1))*( INDEX($B$2:$B$57,E2+1,1)- INDEX($B$2:$B$57,E2,1))+ INDEX($B$2:$B$57,E2,1)
And that worked
Jacob

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

eBook - Simulation-Driven Design with SOLIDWORKS
Simulation-driven design can reduce the time and cost of product development. In this engineering.com eBook, we’ll explore how SOLIDWORKS users can access simulation-driven design through the SOLIDWORKS Simulation suite of analysis tools. Download Now
eBook - Integrating the Engineering Ecosystem
Aras Innovator provides multiple options for integrating data between systems, depending on the scenario. Utilizing the right approach to meet specific business requirements is vital. These needs range from authoring tools, federating data from various and dissimilar databases, and triggering processes and workflows. Download Now
Research Report - Simulation-Driven Design for SOLIDWORKS Users
In this engineering.com research report, we discuss the rising role of simulation and the paradigm shift commonly called the democratization of simulation. In particular, we focus on how SOLIDWORKS users can take advantage of simulation-driven design through two analysis tools: SOLIDWORKS Simulation and 3DEXPERIENCE WORKS. 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