# Averages spread out over several fields in Excel?

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.

-
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.

-

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
``````
-
Thanks for that, you provided me with inspiration to write my own version! – Michael Galos Aug 13 '10 at 3:58

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
``````
-

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.

-