Average if values in adjacent columns is >0 AND adjust the average range each time

Hi Excel Forum!

I have a challenge that I hope someone here can solve.

I am trying to calculate an Average if the values in adjacent column are greater than zero AND shift the range of the average to start and finish for each section of rows that are greater than 0.

Attached is a spreadsheet to give a clearer picture. In A2:A13, I have a sequence of ones and zeroes to indicate when the ranges to average. B2:B13 shows the data I want to average. B2:B13 is the correct answer. I'd like to be able to do this formulaically in column D as the real data set I have has varying number of rows between zeroes and ones and has >30,000 rows.

Thanks in advance!!

ew17

Try in D4:

Please Login or Register  to view this content.

Drag down

Notice that I add one extra "0" in the last blank cell (A14) to make sure formula of last group cells of "1" work