Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I have a table with a sequence of data.
This data is used in a graph.
There are holes in this data that I want to fill in with smoothed out numbers.

Take this table for example:

sample table

In the example above, there are 3 gaps between 93 and 68 which I want to fill in.
The sequence should go 93, 86.75, 80.5, 74.25 and then 68.

What kind of formula could I use to auto-calculate the numbers in between?

Edit: the gaps could be any number of rows, they could be going up or down.

share|improve this question
Are they always decreasing, as you go down, and always positive? – Lance Roberts Aug 12 '10 at 23:50
Will there be more than one gap in the numbers in a column? – Lance Roberts Aug 12 '10 at 23:58
Not always positive and could be any size gap. The time in the left most column will always be in increments of 10 mins and won't have any gaps. – Michael Galos Aug 13 '10 at 0:00
Wow, interpolation is tough when you don't want it to change the increment when you fill the values in, still working on it. – Lance Roberts Aug 13 '10 at 0:21
OK, while I can do the formula for the interpolation, it won't work because when the first cell fills in, it will change the increment on the following cells, and so on. Since I can't really store a variable (for the quantity of blank cells in the gap), I can't do it in a worksheet function. Let me know if you want a VBA solution. I'll be back tomorrow, just reference my name in a comment with an '@' sign. – Lance Roberts Aug 13 '10 at 0:28

If your time intervals are always equally spaced you can use Excel's Fill feature.

  • Select B2285:F2289 (the empty range plus the rows of data above and below)
  • On the Home ribbon tab, select Fill and then Series...
    • Series in: Columns
    • Type: Linear
    • Trend: Yes
    • Click OK

Tested in Excel 2007.

share|improve this answer

With this VBA sub you can select the cells you want to interpolate, then activate the macro. I used a button, but you'll probably want to workup a shortcut key combo.

Private Sub InterpolateGap()

Dim Gap As Range
Dim GapRows As Integer, i As Integer, Increment As Integer

Set Gap = Selection
If Not Gap Is Nothing Then
    GapRows = Gap.Rows.Count
    Increment = (Gap.Cells(1, 1).Offset(-1, 0) - _ 
                 Gap.Cells(1, 1).Offset(GapRows, 0)) / GapRows
    For i = 1 To GapRows
        Gap.Rows(i).Cells(1, 1) = _ 
        Gap.Rows(i).Cells(1, 1).Offset(-1, 0) - Increment
    Next i
End If

End Sub
share|improve this answer
Thanks for that, you provided me with inspiration to write my own version! – Michael Galos Aug 13 '10 at 3:58
up vote 0 down vote accepted

I ended up writing some VBA code thanks @Lance Roberts
This code will actually loop through each row and column searching for blank entries, grabbing the upper and lower values and calculating that way.
The only problem is when the first row if data is blank.
The columns are hard coded as 10 due to laziness.

Sub SetAverages()
   Dim lastrow As Integer, ncol As Integer, nrow As Integer
   Dim secondvalrow As Integer, blankrows As Integer
   Dim difference As Double, Increment As Double

    lastrow = ActiveCell.Row

    For ncol = 2 To 10
        For nrow = 2 To lastrow   'start after header row
            If Cells(nrow + 1, ncol).Value = "" Then
                secondvalrow = nrow + 1
                Do Until Cells(secondvalrow, ncol).Value <> "" Or secondvalrow = lastrow + 1
                    secondvalrow = secondvalrow + 1

                blankrows = secondvalrow - nrow

                difference = Cells(secondvalrow, ncol).Value - Cells(nrow, ncol).Value
                Increment = difference / blankrows
                For i = nrow + 1 To secondvalrow - 1
                    Cells(i, ncol).Value = Cells(i - 1, ncol).Value + Increment
                Next i
            End If
        Next nrow
    Next ncol
End Sub
share|improve this answer

It's going to be hard to do when you don't have a steady upward or downward climb.

My best advice is simply highlighting the numbers you have and then clicking on the box in the bottom right hand corner of the highlighted area, and dragging in down to let Excel do its best job at guessing.

alt text

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .