replace blank cells in Excel with average of values before and after

Question

I have a time series where some records have no value ex.

A1: 1-1-2000 ------------------- B1: 2
A2: 1-2-2000 ------------------- B2:
A3: 1-3-2000 ------------------- B3: 3
A4: 1-4-2000 ------------------- B4: 6
A5: 1-5-2000 ------------------- B5:
A6: 1-6-2000 ------------------- B6: 3
A7: 1-7-2000 ------------------- B7:
A8: 1-8-2000 ------------------- B8: 7
A9: 1-9-2000 ------------------- B9:
A10: 1-10-2000 ----------------- B10:
A11: 1-11-2000 ----------------- B11: 9
A12: 1-12-2000 ------------------- B12: 8

i need to replace blanks with the average of the values nearest before and after

Answer 1

You could use code as below which uses iteration to handle multiple consecutive blanks (as you have in B9:B10).

Sub FillEm()
Dim rng1 As Range
Dim rng2 As Range

Set rng2 = Range("B1:b12")

On Error Resume Next
Set rng1 = rng2.SpecialCells(xlBlanks)
On Error GoTo 0
If rng1 Is Nothing Then Exit Sub

Application.Iteration = True
rng1.FormulaR1C1 = "=AVERAGE(R[-1]C,R[1]C)"
Application.Iteration = False
rng2.Value = rng2.Value

End Sub

Answer 2

Here's a formula version that combines a standard formula for finding the last non-blank cell in a range with a formula for finding the first non-blank cell in a range:-

=IF(B2="",AVERAGE(LOOKUP(2,1/(B$1:B1<>""),B$1:B1),INDEX(B3:B$12,MATCH(TRUE,INDEX((B3:B$12<>""),0),0))),B2)

to be entered in C2 and pulled down.

B1 and B12 must be non-blank.

If the formula is copied into B1, it will show a "#REF!" error within the formula but will give the correct answer if B1 contains a number.