Dismiss Notice
Join Physics Forums Today!
The friendliest, high quality science and math community on the planet! Everyone who loves science is here!

Interpolation of data on Excel for analysis of Load Displacement

  1. Mar 24, 2010 #1
    I don't need any help for the engineering maths I'll be doing after but if I anyone could advise me on how to write a program that would take a set of x and y values and then give me back the y values at certain intervals on the x axis I would be very grateful. I've got colossal amounts of data that needs sorting before I can use it.

  2. jcsd
  3. Mar 24, 2010 #2


    User Avatar

    Staff: Mentor

    Welcome to the PF. I'm not sure I understand your question. What format is the data in? Is it already in Excel in two columns? Or is it in a clear text file in CSV or some other format?

    And you want the output of your program to be what? An Excel spreadsheet with an interpolated y data column next to some pre-set x data (regular interval) column?
  4. Mar 24, 2010 #3


    User Avatar
    Science Advisor

    If you have the math worked out then most of the work will be in making the program interface. Is that what you need help with? If so, it sounds more like a programming issue. If so, then what language are you familiar with?

  5. Mar 24, 2010 #4
    Just do a least squares regression on the data set, and get a function for y(x). Then plug in values of x to the function you get and there's your interpolated outputs!
  6. Mar 24, 2010 #5
    What I have is a few different sets of data, all with two columns in. My problem is that to take an average of all the different data sets, I need x values at set intervals, whereas at the minute I may have a y value at x = 0.9 and another at x = 1.1 and need the value at 1.0. This isn't a problem obviously to interpolate by hand but the values are to a few decimal places and to do the whole lot by hand would take quite a while. I'm pretty clueless with how to use Excel VBA to help me with it. So the output needs to be An Excel spreadsheet with an interpolated y data column next to some pre-set x data (regular interval) column as berkeman suggested.

    Thanks again.
  7. Mar 27, 2010 #6


    User Avatar
    Homework Helper

    If you are using Windows XP, the link below has an Excel add-in called XNUMBERS that contains built in interpolation functions.


    Look for the item at the top of the list called "XNUMBERS 5.6 - Multi Precision Floating Point Computing and Numerical Methods for EXCEL"

    There is also an excellent tutorial:


    Look for the item called “Numeric calculus in Excel” Xnumbers Tutorial vol.1,Oct. 2007 by Foxes Team.
Share this great discussion with others via Reddit, Google+, Twitter, or Facebook