Forum Discussion

cowboy915's avatar
cowboy915
Copper Contributor
Sep 14, 2022

Smoothing Data

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.

  •  
excel
Formulas and Functions

2 Replies

  • mtarler's avatar
    mtarler
    Silver Contributor
    Sep 14, 2022

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

     

    =LET(in,A1:A33,
     n,ROWS(in),
     zeros,ABS(SCAN(0,in,LAMBDA(p,i,IF(i,IF(p>0,-p-1,0),p+1)))),
     revIN,INDEX(in,SEQUENCE(n,1,n,-1)),
     revZ,INDEX(zeros,SEQUENCE(n,1,n,-1)),
     revZhold,LET(a,SCAN(0,revZ,LAMBDA(p,I,IF(p*I,p,I))),IF(a,a,1)),
     revINhold,SCAN(0,revIN,LAMBDA(p,i,IF(i,i,p))),
     revOUT,revINhold/revZhold,
    INDEX(revOUT,SEQUENCE(n,1,n,-1)))

     

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

  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Sep 14, 2022

    cowboy915 

    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
        Loop
        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
    Loop
    Range("C1:C" & m).Value = v
End Sub

Resources