Smoothing Data

Occasional Visitor

I will try and explain what I'm trying to accomplish in the best way possible. Assuming there are millions of rows of data, I need to use formulas to accomplish the following:


  • The values in Column A need to be moved into Column C, however:
    • Any cluster (one or multiple zeros) of zero values (highlighted in Green) in Column A needs to be averaged out with the first "non-zero" value below it (highlighted in Yellow).
    • The average of each cluster needs to be inputted in Column C for the rows with the zeros in Column A and the first non-zero value below them (highlighted in blue)
    • If a value in Column A has no zeros above it, it just transfers directly into Column C.
    • This can be accomplished in multiple steps (columns) if needed.

I am having difficulty with creating a formula, or multiple formulas, to recognize how many zeros are above the first non-zero number below them and average out each (or divide the first non-zero number below each cluster of zeros by the total number of zeros above it + itself) because each cluster may have a different number of zeros in them.


I hope this makes sense.

  • Screen Shot 2022-09-14 at 9.04.33 AM.png 
2 Replies


Here is a macro solution:

Sub AverageOutZeroes()
    Dim v As Variant
    Dim r As Long
    Dim i As Long
    Dim n As Long
    Dim m As Long
    Dim d As Double
    m = Range("A" & Rows.Count).End(xlUp).Row
    v = Range("A1:A" & m).Value
    r = 1
    Do While r <= m
        n = 0
        Do While v(r + n, 1) = 0
            n = n + 1
        If n > 0 Then
            d = v(r + n, 1) / (n + 1)
            For i = 0 To n
                v(r, 1) = d
                r = r + 1
            Next i
        End If
        r = r + 1
    Range("C1:C" & m).Value = v
End Sub

@cowboy915  i created a formula but it isn't "pretty"




maybe someone else could do it easier.  i had to scan arrays and 'reverse' arrays multiple times.  but it seems to work at least