1. I have a data set with data points every 2 weeks, more or less. I would like to do a linear interpolation between the 2 data points to make a synthetic daily data set. The problem lies in that the days between the data points in not constant. Right now, there are blank cells between the data points.

any idea on how to do this?
I'd settle for just copying the first data point down to the second and 'stairstep' the data.

Thanks.

2. [quote name='jepalmer' post='793939' date='18-Sep-2009 16:51']I have a data set with data points every 2 weeks, more or less. I would like to do a linear interpolation between the 2 data points to make a synthetic daily data set. The problem lies in that the days between the data points in not constant. Right now, there are blank cells between the data points.

any idea on how to do this?
I'd settle for just copying the first data point down to the second and 'stairstep' the data.

Thanks.[/quote]

Are there an exact quantity of adjacent blanks to fill in the missing days?

Can you provide a sample of what you currently have? With meaningless data of course.

3. [quote name='wdwells' post='793941' date='18-Sep-2009 21:59']Are there an exact quantity of adjacent blanks to fill in the missing days?

Can you provide a sample of what you currently have? With meaningless data of course.[/quote]
no, the adjacent blank number varies
example attached

Thanks!

4. Try this macro:

Code:
```Sub PiecewiseInterpolation()
Const FirstRow = 1
Const DataCol = 2
Dim r As Long
Dim LastRow As Long
Dim r1 As Long
Dim r2 As Long
Dim y1 As Double
Dim y2 As Double
LastRow = Cells(Rows.Count, DataCol).End(xlUp).Row
For r = FirstRow To LastRow
If Not Cells(r, DataCol) = "" Then
r1 = r
y1 = Cells(r1, DataCol)
r2 = Cells(r + 1, DataCol).End(xlDown).Row
y2 = Cells(r2, DataCol)
Else
Cells(r, DataCol) = y1 + (r - r1) / (r2 - r1) * (y2 - y1)
End If
Next r
End Sub```

5. [quote name='jepalmer' post='793944' date='18-Sep-2009 17:23']no, the adjacent blank number varies
example attached

Thanks![/quote]

If you are interested in the work which Hans has saved you; take a look at the attached. The final results show up in column J. and wonder of wonders, they match Hans' results.

6. [quote name='wdwells' post='793971' date='19-Sep-2009 00:26']If you are interested in the work which Hans has saved you; take a look at the attached. The final results show up in column J. and wonder of wonders, they match Hans' results.[/quote]
Gentlemen:
Thank you. You have saved me hours of brute force Excel work.
On a weekend, no less

THANKS!

#### Posting Permissions

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