Filling cells in between pairs of numbers

[Nik]

Hello all,

I am trying to find an easy way to fill in cells in between pairs of
numbers each of which is exactly the average of the pair's difference
averaged by the number of these cells greater than the previous.

So I have
1 1
2
3
4 8
5
6
7
8 20
9
10
12
13
14
15 45

I want:
1 1
2 3.333333333
3 5.666666667
4 8
5 11
6 14
7 17
8 20
9 23.57142857
10 27.14285714
11 30.71428571
12 34.28571429
13 37.85714286
14 41.42857143
15 45

I know I can do a Fill, Series, but then even with a macro that does a
"control shift up fill series enter", it would take me forever for I
have easily 100,000 such entries.

Is there any faster ways?

Thanks a million!

 

[Rick Rothstein \(MVP - VB\)]

Give this macro a try (just set the start row for your first piece of data
in the StartRow constant and the column where you data is at in the
DataColumn constant at the beginning of the macro)...

Sub FillAverages()
Const StartRow As Long = 1
Const DataColumn As String = "A"
Dim C As Range
Dim X As Long
Dim CurRow As Long
Dim CurVal As Double
Dim Average As Double
Set C = Cells(StartRow, DataColumn)
Do While C.End(xlDown).Row < Rows.Count
CurVal = C.Value
CurRow = C.Row
Set C = C.End(xlDown)
Average = (C.Value - CurVal) / (C.Row - CurRow)
For X = CurRow + 1 To C.Row - 1
Cells(X, DataColumn).Value = Cells(X - 1, DataColumn).Value + Average
Next
Loop
End Sub

Rick