CODEWITHSUNDEEP
excelexcel-2010vba
amit_183
amit_183

Reputation: 981

automate interpolation of time-stamped data in excel

I have existing data collected at 6 minute interval. Time stamps for which are in column A and the corresponding values are in column B.

I want to interpolate this data to get a minute by minute breakdown, calculated using a basic interpolation method. A sample of the desired output is shown in columns D and E.

Is there a way to automate this calculation?enter image description here

Upvotes: 0

Views: 858

Answers (2)

lori_m
lori_m

Reputation: 5567

You could try filling down from E1:

=PERCENTILE(B$1:B$7,PERCENTRANK(A$1:A$7,D1,30))

(assumes values are non-decreasing.)

Upvotes: 2

twegner
twegner

Reputation: 443

I wrote my own interpolate function. It is not designed for dates so you will need to modify the parameters. It might be a good starting point for you. The first parameter is the data point you want to interpolate on. the second parameter is the range of input data defining your curve. Note, when a data point is out of bounds the return value will be the nearest data point, not an extrapolated value.

Function interpolate(inp As Double, rng As Range)
' written by: Todd Wegner
'
'  inp --> the data point to interpolate
'  rng --> the range of original data
'
' early binding
Dim i As Long, dim1 As Long
Dim x1 As Double, x2 As Double, y1 As Double, y2 As Double
Dim arr As Variant
'
' set array to the Range(rng)
  arr = rng
'
' if the input is below the data bounds
' use the first data point, DO NOT extrapolate
  If inp < arr(1, 1) Then
    interpolate = arr(1, 2)
    Exit Function
  End If
'
' get array upper bound
  dim1 = UBound(arr)
'
' if the input is above the data bounds
' use the last data point, DO NOT extrapolate
  If inp > arr(dim1, 1) Then
    interpolate = arr(dim1, 2)
    Exit Function
  End If
'
' inputs that lie within the data range get interpolated
  i = 1
  Do Until inp < arr(i, 1)
    i = i + 1
  Loop
  x1 = arr(i - 1, 1)
  x2 = arr(i, 1)
  y1 = arr(i - 1, 2)
  y2 = arr(i, 2)
'
' return
  interpolate = (y2 - y1) / (x2 - x1) * (inp - x1) + y1
'
End Function

Upvotes: 0

Related Questions