Linear Interpolation Between Multiple Points

[star_luna]

Dear Excel friends,

I want to linearly interpolate between multiple points. I have a column with values and empty cells. The values are not equidistant from each other. Excel allows me to linearly interpolate between two values using Fill>Series... , however, when I select multiple values it overrides the values in between the top and bottom cell.

I have attached a snippet of part of my column. I want to be able to select the entire section and that it interpolates between -0.0630 and -0.0740 and between -0.0740 and -0.0820 and so forth ...

Many thanks in advance for your help,
Luna

 

[Joe4]

Welcome to the Board!

I must confess, I have not used any of the Excel tools to do this before.
So can you show us how exactly you are doing it now in Excel?

I am thinking that you need to interpolate between each two points individually (and not all at once).
You might be able to use VBA to do each one in succession all at once, so you don't have to.

If VBA is an option, show us how you do it now, and we will see if we can help you use VBA to automate it.

 

[Jon Peltier]

You need a VBA routine to do the Fill > Series for you as often as you need it done. You also have to calculate what the step is between cells, but you can find the first and last value and count how many cells are involved. This might be just what you want. Select the first cell with a value in the column, then run the code.

Sub MultipleInterpolationsInColumn()
  Dim FirstCell As Range
  Dim LastCell As Range
  Set FirstCell = Selection
  Do
    Set LastCell = FirstCell.End(xlDown)
    If LastCell.Row = ActiveSheet.Rows.Count Then
      Exit Do
    End If
    Range(FirstCell, LastCell).DataSeries xlColumns, xlDataSeriesLinear, _
      xlDay, (LastCell.Value - FirstCell.Value) / (LastCell.Row - FirstCell.Row)
    Set FirstCell = LastCell
  Loop
End Sub

 

[star_luna]

You need a VBA routine to do the Fill > Series for you as often as you need it done. You also have to calculate what the step is between cells, but you can find the first and last value and count how many cells are involved. This might be just what you want. Select the first cell with a value in the column, then run the code.

Sub MultipleInterpolationsInColumn()
  Dim FirstCell As Range
  Dim LastCell As Range
  Set FirstCell = Selection
  Do
    Set LastCell = FirstCell.End(xlDown)
    If LastCell.Row = ActiveSheet.Rows.Count Then
      Exit Do
    End If
    Range(FirstCell, LastCell).DataSeries xlColumns, xlDataSeriesLinear, _
      xlDay, (LastCell.Value - FirstCell.Value) / (LastCell.Row - FirstCell.Row)
    Set FirstCell = LastCell
  Loop
End Sub

Hi Jon!
Thank you so much for your reply. When I run it fills in the first blank cells, however when it reaches the next value it gives an error 'type mismatch' and points a yellow arrow to line 11 in your code. Do you maybe know how to handle this error?

 

[Jon Peltier]

Which line are you calling line 11?

 

[star_luna]

xlDay, (LastCell.Value - FirstCell.Value) / (LastCell.Row - FirstCell.Row)

Which line are you calling line 11?

This one !

 

[Jon Peltier]

You may have nonnumeric data in one or more cells in the range. Make sure the blank cells between numbers are really blank (not formulas that appear blank) and that the numbers are not stored as text.

 

[star_luna]

Ok that makes sense because the blanks inbetween the values are formula generated (""). I read that I can't generate 'true' blanks in excel. Do you maybe have a suggestion how I could transform my data so it can be processed by your code?

 

[Jon Peltier]

This routine first clears out any formulas that return "", then it does the interpolation. Note that the interpolation changes formulas that return numbers into just cells with values (except the very first cell)

Sub MultipleInterpolationsInColumn()
  Dim FirstCell As Range
  Set FirstCell = Selection
  Dim WholeRange As Range
  Set WholeRange = Range(FirstCell, FirstCell.End(xlDown))
  Dim OneCell As Range
  For Each OneCell In WholeRange.Cells
    If OneCell.HasFormula And Len(OneCell.Value) = 0 Then
      OneCell.ClearContents
    End If
  Next
  Do
    Dim LastCell As Range
    Set LastCell = FirstCell.End(xlDown)
    If LastCell.Row = ActiveSheet.Rows.Count Then
      Exit Do
    End If
    Range(FirstCell, LastCell).DataSeries xlColumns, xlDataSeriesLinear, _
      xlDay, (LastCell.Value - FirstCell.Value) / (LastCell.Row - FirstCell.Row)
    Set FirstCell = LastCell
  Loop
End Sub