Q:

Find average of non-zero numbers preceding a zero?

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?

A:

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.

