0

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

Community
  • 1
  • 1
amit_183
  • 961
  • 3
  • 19
  • 36
  • Reading [how to ask a good question](http://stackoverflow.com/help/how-to-ask) will help you get an answer sooner. Remember, this isn't a code-writing service, so post what you've got & we can help you fix it. – FreeMan May 20 '15 at 12:39
  • Entirely agree with FreeMan. In order to encourage improvement of your question, there are several methods that can be applied formulas, macros, but basically we need to know what you are looking for. Also I wonder if the numbers for the gap between 20/05/2015 07:06 13218.30000 and 20/05/2015 07:12 13218.40000 should not be 20/05/2015 07:06 13218.35000 20/05/2015 07:07 13218.40000 20/05/2015 07:08 13218.45000 20/05/2015 07:09 13218.50000 20/05/2015 07:10 13218.55000 20/05/2015 07:12 13218.40000 Just curious about it. – EEM May 20 '15 at 13:14
  • This is related to [this answer](http://stackoverflow.com/a/2824094/4288101). The idea is that you `FORECAST` on a 2x2 grid of dates and values. You get the grid using `OFFSET` and `MATCH`. Other site with formulas to review include [this](http://www.blueleafsoftware.com/Products/Dagra/LinearInterpolationExcel.php) and [this](http://www.mrexcel.com/forum/excel-questions/138021-interpolate-between-two-dates.html). Google is your friend with [this search](https://www.google.com/search?q=excel+two+date+interpolation). – Byron Wall May 20 '15 at 15:38
  • 1
    possible duplicate of [Interpolating data points in Excel](http://stackoverflow.com/questions/1043513/interpolating-data-points-in-excel) – Byron Wall May 20 '15 at 15:39

2 Answers2

2

You could try filling down from E1:

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

(assumes values are non-decreasing.)

lori_m
  • 5,487
  • 1
  • 18
  • 29
  • I don't believe this works in a wide range of cases. I can create some simple examples where it returns unexpected outputs. Are there limitations to the input data (dates and values) that can be used? Example: Dates: `5/21, 5/22, 5/23`, Values: `10,8,18`, Input: `5/22`, Output:`10` where it should be `8`. It is also wrong on other non-exact matching scenarios. – Byron Wall May 20 '15 at 20:51
  • Thanks for adding the conditions. In my experience, data rarely goes one direction so this formula caught me off guard. – Byron Wall May 20 '15 at 21:45
  • You're right it's always better to clarify conditions for applicability. From a sample of interpolation examples posted in groups most are increasing or decreasing relationships. If you want to apply to a decreasing relationship you can use the same formula with `1-PERCENTRANK` – lori_m May 20 '15 at 21:58
0

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
twegner
  • 443
  • 1
  • 5
  • 21