# Smoothing Data

Occasional Visitor

# 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.

•
2 Replies

# Re: Smoothing Data

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``````

# Re: Smoothing Data

@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