I have the following matrix, please relate to the Date an Value columns:
Date Value Date Diff Hours Diff
29/12/2014 8:00 24.940
29/12/2014 9:00 24.960 0.04 1
29/12/2014 10:00 1
29/12/2014 11:00 25.020 0.08 1
29/12/2014 12:00 1
29/12/2014 13:00 1
29/12/2014 14:00 1
29/12/2014 15:00 25.070 1
As can be seen there are missing values sometime of one line sometimes more than one line (the number of missing lines is dynamic). I would like to make an interpolation and to calculate the missing values by using linear interpolation. (The matrix might contain large number of lines).
The final result should be as followed:
Date Value Date Diff Hours Diff
29/12/2014 8:00 24.940
29/12/2014 9:00 24.960 0.04 1
29/12/2014 10:00 24.99 1
29/12/2014 11:00 25.020 0.08 1
29/12/2014 12:00 25.0325 1
29/12/2014 13:00 25.045 1
29/12/2014 14:00 25.0575 1
29/12/2014 15:00 25.070 1
1 Answer
I believe your best method would be a Range.DataSeries method with a computed Step parameter.
Sub seriesFill()
Dim rng As Range
With Worksheets("Sheet11")
With .Cells(1, 1).CurrentRegion
Set rng = .Cells(.Rows.Count, 2)
Do While CBool(Application.CountBlank(.Columns(2)))
With .Range(rng, rng.End(xlUp))
.DataSeries Rowcol:=xlColumns, Type:=xlLinear, _
Step:=(.Cells(.Cells.Count).Value2 - .Cells(1).Value2) / (.Rows.Count - 1)
End With
Set rng = .Cells(.Rows.Count, 2).End(xlUp)
Loop
End With
End With
End Sub
Your results should be similar to the following. This can be manually applied with the Home ► Editing ► Fill ► Series command but a large number of repetitions makes a sub procedure the more viable method.
