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:

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.

Michael Galos

Posted 2010-08-12T23:42:39.460

Reputation: 755

Are they always decreasing, as you go down, and always positive? – Lance Roberts – 2010-08-12T23:50:45.030

Will there be more than one gap in the numbers in a column? – Lance Roberts – 2010-08-12T23:58:01.563

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 – 2010-08-13T00:00:20.453

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 – 2010-08-13T00:21:56.107

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 – 2010-08-13T00:28:38.830

I thought of one thing. If there is only one gap at a time per column (of any size) then you could put a row at the top that keeps the number of blank cells in that gap in it. It may have the same problem, but maybe not. Then it could be referenced by the interpolation formula. – Lance Roberts – 2010-08-13T00:35:45.283

OK, did up a VBA thing before I left. – Lance Roberts – 2010-08-13T01:13:52.247

Answers

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

Michael Galos

Posted 2010-08-12T23:42:39.460

Reputation: 755

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.

Mike Fitzpatrick

Posted 2010-08-12T23:42:39.460

Reputation: 15 062

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

Lance Roberts

Posted 2010-08-12T23:42:39.460

Reputation: 7 895

Thanks for that, you provided me with inspiration to write my own version! – Michael Galos – 2010-08-13T03:58:42.483

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

Ryan

Posted 2010-08-12T23:42:39.460

Reputation: 1 488