Forum Discussion
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 Colu...
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