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

1. ## 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

2. ## 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

3. ## 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:
`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

4. ## 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

5. ## 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:
`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:
`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).

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

thanks for this explanation. works nice!

its fine

8. ## 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. ## 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. ## Re: interpolate a series over another series with the same first and last date

solved thanks!

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