+ Reply to Thread
Results 1 to 10 of 10

interpolate a series over another series with the same first and last date

  1. #1
    Registered User
    Join Date
    03-09-2012
    Location
    paris,france
    MS-Off Ver
    Excel 2007
    Posts
    16

    interpolate a series over another series with the same first and last date

    I am having trouble interpolating the values of two data series. I have a reference time in first column. The second column is time linked for values of P130. I want to interpolate new values of P130 (third column) according to reference time.

    The reference time and timeP130 have the first and last value the same and they are all in variable steps, so there is no pattern.

    Reference_time timeP130 P130 results
    0.0001 0.0001 0.2194 0.2194
    0.000694 0.003 0.25 0.22552
    0.00138889 0.0035 0.26 0.23164
    0.00208333 0.006 0.24 0.23776
    0.00277778 0.009 0.245 0.24388
    0.003 0.009 0.255 0.25
    0.00416667 0.0125 0.27 ETC
    0.00486111 0.015 0.21
    0.00555556 0.018 0.20
    0.00625 0.0208 0.2194
    0.00694444 0.021 0.2194
    0.00763889 0.0211 0.2194
    0.00833333 0.0215 0.2194
    0.00902778 0.022 0.2195
    0.00972222 0.0327 0.2591
    0.0104167 0.0433 0.3664
    0.0111111 0.0839 0.4068
    0.0118056 2.5 0.4087
    0.0125 0.27
    0.0141944
    0.0158889
    0.0165833
    0.0182778
    2.5 0.4087

    Do you know how I can do that with Excel?

    thanks
    Attached Files Attached Files
    Last edited by sensation; 10-21-2015 at 05:57 AM.

  2. #2
    Forum Guru
    Join Date
    04-13-2005
    Location
    North America
    MS-Off Ver
    2002/XP and 2007
    Posts
    13,855

    Re: interpolate a series over another series with the same first and last date

    One solution (if I am allowed to suggest it on an Excel forum): Switch to QuattroPro (or other spreadsheet that has it) and use its @LINTERP() function.

    Since Excel does not offer a built in linear interpolation function, you will need to build it. The basic steps to one approach to linear interpolation:

    1) Using LINEST() function (https://support.office.com/en-us/art...a-fa7abf772b6d ), compute slope and intercept for each pair of points.
    2) Using a lookup function (https://support.office.com/en-us/art...8-93a18ad188a1 VLOOKUP() is one possibility), lookup the computed slope and intercept computed in 1 for the interval containing your desired x.
    3) Use the equation of a straight line to find your desire y y=m*x+b where m and b are the slope and intercept you looked up in step 2.

    I created a simple example of this approach (for a ln(y)=m*ln(x)+b type function) is in this post: http://www.excelforum.com/excel-char...ml#post3904113
    Quote Originally Posted by shg
    Mathematics is the native language of the natural world. Just trying to become literate.

  3. #3
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    7,902

    Re: interpolate a series over another series with the same first and last date

    So with linear interpolation between points you coukld use such (not so short :-P ) formula for D2 (and copy down):
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    It is much easier if you base on few extra columns - see Sheet2 in the attachment.

    PS. probably some pre-processing of the data would be needed, because you have such non-smooth ranges like B6:C7
    0,009 0,245
    0,009 0,255

    Just to check - there is a graph for the first few values (not including dramatic increase 0,0125 -> 2,5 in last range
    Attached Files Attached Files
    Best Regards,

    Kaper

  4. #4
    Registered User
    Join Date
    03-09-2012
    Location
    paris,france
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: interpolate a series over another series with the same first and last date

    Hi Kaper,

    I tried to understand the formula what is not so easy, if you can explain me in few sentences I would appreciated it!

    I try to implement the formula to the whole time series but there is a reference error. Do you know where it comes from?

    Thanks and cheers,

    M
    Attached Files Attached Files

  5. #5
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    7,902

    Re: interpolate a series over another series with the same first and last date

    Have you had a chance sto see sheet2?
    in K2 there is basic formula:
    Formula: copy to clipboard
    Please Login or Register  to view this content.
    so it is just stright line equation with a slope (A2-F2)/(G2-F2) see headers in columns F:I to see what is F2 etc.
    so in main eqyation if we are in a point where values in A and B match we can use first part of iFERROR :
    Formula: copy to clipboard
    Please Login or Register  to view this content.

    Try this and it will return proper values in rows 2, 7, 20, 25
    In other rows, where this stright pulling of excact value from column C fails we have to use interpolation (as above).
    Last edited by Kaper; 10-21-2015 at 09:48 AM.

  6. #6
    Registered User
    Join Date
    03-09-2012
    Location
    paris,france
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: interpolate a series over another series with the same first and last date

    thanks for this explanation. works nice!

  7. #7
    Registered User
    Join Date
    03-09-2012
    Location
    paris,france
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: interpolate a series over another series with the same first and last date

    its fine
    Attached Files Attached Files
    Last edited by sensation; 10-22-2015 at 09:52 AM.

  8. #8
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    7,902

    Re: interpolate a series over another series with the same first and last date

    1) You do not need values below D1615 - as you have no data in A1615 and below
    2) to use this metod you need last (largest) value in column B to be at least the same or bigger than last value in column A in your case its not true -
    A1614 is 2,68055
    while
    B3257 only
    2,6805

  9. #9
    Forum Guru Kaper's Avatar
    Join Date
    12-14-2013
    Location
    Warsaw, Poland
    MS-Off Ver
    most often 365, but still time to time use also older versions
    Posts
    7,902

    Re: interpolate a series over another series with the same first and last date

    PS. as last few values in B and C columns are
    2,6771 0,2809
    2,6771 0,2809
    2,6805 0,2809
    you can probably make a small extrapolation to
    2,6771 0,2809
    2,6771 0,2809
    2,6805 0,2809
    2,6806 0,2809
    and then all D2:D1614 cells will return reasonable numbers.

  10. #10
    Registered User
    Join Date
    03-09-2012
    Location
    paris,france
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: interpolate a series over another series with the same first and last date

    solved thanks!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 2
    Last Post: 11-11-2013, 05:37 PM
  2. VBA code to extract info from a series of URLs within a series of web pages
    By holmdeal in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-02-2012, 07:25 PM
  3. Replies: 2
    Last Post: 12-13-2010, 01:39 PM
  4. mixing column chart types for series and macro series?
    By riwiseuse in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 03-30-2010, 05:27 PM
  5. Replies: 4
    Last Post: 01-06-2009, 04:28 PM
  6. chart data series -- plot a table as a single series
    By hjc in forum Excel Charting & Pivots
    Replies: 7
    Last Post: 09-20-2005, 01:05 PM
  7. series graph -- one series being added to another series
    By rich zielinski via OfficeKB.com in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 03-30-2005, 02:06 PM
  8. Filling in a Date Series using the Fill | Series menu command
    By Bob C in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 02-01-2005, 08:06 PM

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1