[SOLVED] How to ask Excel carry on infinitely when it is right and stop once it is wrong?

Sorry that I have the following problem:
Now I've got the data of the left column (there are only yes and no in the column) and I hope to input a formulae to generate the right column.
The rationale is like this:
if left column is 'no', then the right column is 0
if left column is 'yes', then:
a. continue to check if the cell above is 'yes', if it is, value of right column plus one and repeat the process until a 'no' appears;
b. if the cell above is 'no', then the right column is also 0.

I understand it's a bit abstract, but thank you very much for answering my query!

In order for this to work, row 1 needs to be a header row so that actual data starts in row 2. In then in cell B2 use this formula and copy down:

Please Login or Register  to view this content.