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.
Just to show a completely different approach:

```
Public Function special_average(rng As Range)
  If rng.Value = 0 Then special_average = "": Exit Function
  Dim i As Long, lower_rng As Range, str As String
  Set lower_rng = rng

  If rng.Row > 1 Then str = rng.Offset(-1, 0).Text
  While str <> "0" And Len(str) > 0 And IsNumeric(str) And rng.Row > 1
    Set rng = rng.Offset(-1, 0)
    str = rng.Offset(-1, 0).Text
  Wend

  If lower_rng.Row < Rows.Count Then str = lower_rng.Offset(1, 0).Text Else str = ""
  While str <> "0" And Len(str) > 0 And IsNumeric(str) And lower_rng.Row < Rows.Count
    Set lower_rng = lower_rng.Offset(1, 0)
    str = lower_rng.Offset(1, 0).Text
  Wend

  special_average = Application.WorksheetFunction.Average(rng.Parent.Range(rng, lower_rng))
End Function

```

In this case you just need to input one single cell. It will automatically go up and down to get the range to average. So for `B5` you just need `=special_average(A5)`.

Still, I would use the answer of *Grade 'Eh' Bacon*. It will be fast and also work if macros are disabled.

**EDIT:** To show a different approach to the formula way (without a helper column), you could put in `B2` and then copy down:

```
=IF(A2=0,"",IF(ISNUMBER(B1)*(B1<>0),B1,IFERROR(AVERAGE(OFFSET(A2,0,0, MATCH(0,A2:A1000,0)-1)),AVERAGE(A2:A1000))))

```

The downside is, that you need to set a max range (999 Rows in this example, which can be extended if necessary)