Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [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.
    Regards
    Don

  3. #3
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [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!
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    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. #5
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post
    [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.
    Attached Files Attached Files
    Regards
    Don

  6. #6
    4 Star Lounger
    Join Date
    May 2002
    Posts
    410
    Thanks
    1
    Thanked 0 Times in 0 Posts
    [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
  •