Option Explicit
Option Base 1

Function Average_Remains(DateX As Range, Remains As Range) As Double

'Author:    Roman Rioran Voronov
'Date:      the 29-th of September, 2014
'Feedback:  voronov_rv@mail.ru

Dim arrX As Variant
Dim sizeX As Long
Dim Day1 As Long
Dim X As Long
Dim A As Long

Day1 = DateX.Cells(1, 1).Value
sizeX = DateX.Cells(DateX.Rows.Count, 1).Value - Day1 + 1

ReDim arrX(1 To sizeX)

For X = 1 To UBound(arrX)
     arrX(X) = Day1 - 1 + X
     Average_Remains = Average_Remains + Remains.Cells(A + 1, 1).Value
     If arrX(X) + 1 = DateX.Cells(A + 2, 1) Then A = A + 1
Next X

Average_Remains = Average_Remains / UBound(arrX)

End Function