linear interpolation
 

Hi,

I have a set of data that has monthly (or sometimes biweekly) measurements.
However, I need to interpolate the daily values using the actual consecutive
data. However, I do not want to put a best-fit line or curve through the data
for the whole year. I want the program to pick between two consecutive
values, and depending on their relationship (linear increase or decrease),
fill in the gaps between them and spit out the corresponding values. For
example, I have the following set,

Date Value
Jan. 10 0
Jan. 15 5

what I need is this:

Date Value
Jan. 10 0
Jan. 11 1
Jan. 12 2
Jan. 13 3
Jan. 14 4
Jan. 15 5

However, the next day could be Feb. 2 and the values could be negative and I
want the program start filling the days between Jan. 15 to Feb. 2 without
using the Jan. 10 value. I hope I am making myself clear. I dont know if
there is a program, macro or a function that can do that. I would appreciate
if you could help me.

Thanks in advance,

Taha

You could try this:

Assume that your dates are in A2:A7 and your values are in B2:B7. B3:B6 are
blank.

Put the following formula in B3, and then copy it down for B4,B4,B5 and B6.

=$B$2+($B$7-$B$2)*(A3-$A$2)/($A$7-$A$2)

Art
"Taha" wrote:

Hi,

I have a set of data that has monthly (or sometimes biweekly) measurements.
However, I need to interpolate the daily values using the actual consecutive
data. However, I do not want to put a best-fit line or curve through the data
for the whole year. I want the program to pick between two consecutive
values, and depending on their relationship (linear increase or decrease),
fill in the gaps between them and spit out the corresponding values. For
example, I have the following set,

Date Value
Jan. 10 0
Jan. 15 5

what I need is this:

Date Value
Jan. 10 0
Jan. 11 1
Jan. 12 2
Jan. 13 3
Jan. 14 4
Jan. 15 5

However, the next day could be Feb. 2 and the values could be negative and I
want the program start filling the days between Jan. 15 to Feb. 2 without
using the Jan. 10 value. I hope I am making myself clear. I dont know if
there is a program, macro or a function that can do that. I would appreciate
if you could help me.

Thanks in advance,

Taha

Hi Taha,

If you insert into cells A1 through D24:

10-Jan-2005 0.000
11-Jan-2005 XXX 1 6
12-Jan-2005 XXX 1 6
13-Jan-2005 XXX 1 6
14-Jan-2005 XXX 1 6
15-Jan-2005 5.000
16-Jan-2005 XXX 6 24
....
02-Feb-2005 -13.000

and replace XXX by
=OFFSET($B$1,C2-1,0)+(OFFSET($B$1,D2-1,0)-OFFSET($B$1,C2-
1,0))*(A2-OFFSET($A$1,C2-1,0))/(OFFSET($A$1,D2-1,0)-OFFSET
($A$1,C2-1,0))

in cell B2 and copy it onto the other XXX cells, this
should work fine. Hint: Values in columns C and D tell the
formula where to look for given values (row number).

HTH,
Bernd

Hi Taha

Assuming

in A1:B100 (headings in A1:B1)

Date Value
Jan. 10 0
Jan. 15 5
Feb. 2 -12
etc.


In D1:E1: headings
In D2: =A2
In D3: =D2+1
Copy D3 down

In E2 this formula. The formula must be entered/copied as one line.

=IF(COUNTIF($A$2:$A$100,D2),INDEX($B$2:$B$100,MATC H(D2,$A$2:$A$100,0)),
E1-((INDEX($B$2:$B$100,MATCH(D2,$A$2:$A$100))-(INDEX($B$2:$B$100,
MATCH(D2,$A$2:$A$100)+1)))/(INDEX($A$2:$A$100,MATCH(D2,$A$2:$A$100)+1)-
(INDEX($A$2:$A$100,MATCH(D2,$A$2:$A$100

Doubleclick the fill handle in E2 (the little square in the lower right
corner of the cell) to copy the formula down.

--
Best Regards
Leo Heuser

Followup to newsgroup only please.

"Taha" skrev i en meddelelse
...
Hi,

I have a set of data that has monthly (or sometimes biweekly)
measurements.
However, I need to interpolate the daily values using the actual
consecutive
data. However, I do not want to put a best-fit line or curve through the
data
for the whole year. I want the program to pick between two consecutive
values, and depending on their relationship (linear increase or decrease),
fill in the gaps between them and spit out the corresponding values. For
example, I have the following set,

Date Value
Jan. 10 0
Jan. 15 5

what I need is this:

Date Value
Jan. 10 0
Jan. 11 1
Jan. 12 2
Jan. 13 3
Jan. 14 4
Jan. 15 5

However, the next day could be Feb. 2 and the values could be negative and
I
want the program start filling the days between Jan. 15 to Feb. 2 without
using the Jan. 10 value. I hope I am making myself clear. I dont know if
there is a program, macro or a function that can do that. I would
appreciate
if you could help me.

Thanks in advance,

Taha