Populate blank cells with average of nearest non-blank values

[chibibenz]

Hello,

I am looking for a formula or macro to help me populate blank cells. The situation is that I have minute-by-minute data, but there are some missing minutes. Sometimes a single minute is missing, sometimes a chunk of minutes is missing. I have a macro that inserts a row every time there is a missing minute. Where I am stuck is that I need to fill in data for the missing minutes with the average of the nearest non-blank before and after.

For a single minute of missing data, the formula is very easy. I can just enter =AVERAGE(R[-1]C,R[1]C).
For two minutes of missing data, the first minute will have the formula =AVERAGE(R[-1]C,R[2]C), and the second minute of missing data has the formula =AVERAGE(R[-2]C,R[1]C). (and so on).

I have a TON of missing minutes to populate, and the fact that I can't just make a formula and drag it down across all the missing rows is making this very time consuming. Can you think of a way to write the formula that would automate this? I'm thinking maybe some kind of indirect function but I'm just not sure. I'm open to adding helper columns, anything to speed up the process of populating all these empty rows! Thank you!

 

[Akuini]

Maybe something like this:

Sub a1125754a()
'https://www.mrexcel.com/board/threads/populate-blank-cells-with-average-of-nearest-non-blank-values.1125754/
Dim i As Long, j As Long, n As Long
Application.ScreenUpdating = False
n = Range("A" & Rows.Count).End(xlUp).Row
For i = 2 To n - 1
    If Cells(i, "A") = "" Then
        j = i
        Do While Cells(i, "A") = ""
            i = i + 1
        Loop
        Range(Cells(j, "A"), Cells(i - 1, "A")) = (Cells(j - 1, "A") + Cells(i, "A")) / 2
    End If
Next

Application.ScreenUpdating = True
End Sub

Example:

Book1 (version 1).xlsb
	A
1	data
2	1
3
4	3
5
6
7
8
9	5
10	7
11
12
13	10
Sheet1

Result:

Book1 (version 1).xlsb
	A
1	data
2	1
3	2
4	3
5	4
6	4
7	4
8	4
9	5
10	7
11	8.5
12	8.5
13	10
Sheet1

 

[chibibenz]

THANK YOU! That worked perfectly!!

 

[Akuini]

You're welcome, glad to help, & thanks for the feedback.