Question: I have a column with numbers and I want to calculate the average of all non-zero numbers preceding a zero. E.g.: If I have a column called Units, I would expect the Average column to get populated if I write the formula.

```
Units Average
32     32 
33     32
31     32
 0      0
 0      0
 1      2
 2      2
 3      2
 0      0
 1      1.5
 2      1.5

```

Is there a way in excel to perform this using formula?

Answer: 
You can do this quite simply with formulas if you just use a single helper column, as follows [assumes that your data is in column A starting at A2, your helper column is in column B starting at B2, and your results will be in column C starting at C2]:

Helper column [Put in B2 and drag down]:

```
=IF(A2=0, "", IF(A1=0,MAX(B$1:B1)+1,B1))

```

This creates a unique identifier that iterates upwards by 1 each time there is a non-zero value in column A, and the value in the row above is 0. ie: a group of rows will all have the same ID number until a row with a 0 appears - then the next block of numbers will have an ID number which is 1 higher than the last group. Note that B2 will need to be hardcoded to 1, or else make A1 equal to 0. 

Results column in [C2 and drag down]:

```
=AVERAGEIFS(A:A,B:B,B2)

```

This picks up the average in column A, for all rows matching the identifier created in the helper column.
Not quite as simple and short as I would have whished for but, nonetheless, here it is:

```
Option Base 1
Function myavg(r As Range)
  Dim i%, i0%, j%, n%, ia, a, oa()
  ia = r
  n = UBound(ia, 1)     ' get number of rows
  ReDim oa(n, 1)        ' prepare an output array with same no of rows
  a = 0:  i0 = 0        ' a:  accumulator summing up values
                        ' i0: position of last 0 value found
  For i = 1 To n        ' go through all input rows
    If (ia(i, 1) = 0) Then ' current value is zero:
      oa(i, 1) = 0         ' set output value to 0
     If i > i0 + 1 Then
      a = a / (i - i0 - 1)    ' calculate average value of previous group
      For j = i0 + 1 To i - 1 ' and assign it to output array
        oa(j, 1) = a
      Next j
      a = 0                ' reset the accumulator
     End If
     i0 = i
    Else
     a = a + ia(i, 1)      ' add to accumulator
    End If
  Next i

  If i > i0 + 1 Then       ' do average calculation for last group
   a = a / (i - i0 - 1)    ' if it was not ended with a 0 value
   For j = i0 + 1 To i - 1
       oa(j, 1) = a
   Next j
  End If

  myavg = oa
End Function

```

The user-defined worksheet function works as a matrix-function. This means you will have to select the target range (i.e. `C2:C12)`), enter the function like `=myavg(B2:B12)` and then press `<shift><ctrl><return>`. This will apply the function as a matrix function.

[![sample worksheet table with matrix function entered](https://i.stack.imgur.com/IJRbl.png)](https://i.stack.imgur.com/IJRbl.png)

The function will then appear to be surrounded by curly brackets `{}` like

[![matrix function appearance](https://i.stack.imgur.com/GwNhF.png)](https://i.stack.imgur.com/GwNhF.png)