Sep 14 2022 08:22 AM
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:
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.
Sep 14 2022 08:42 AM
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
Sep 14 2022 09:26 AM - edited Sep 14 2022 09:31 AM
@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