Averages spread out over several fields in Excel?

Question

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:

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.

Answer 1

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.

Answer 2

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

Answer 3

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


    Range("A65535").End(xlUp).Select
    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
                Loop

                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

Answer 4

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.