Try this

Col A - has "yes", "no"

In Col B - cell B1 put 1 and drag it down, use fill series
( 1,2,3,4,5,6.....)

In Col C - in Cell C2 put this formula =IF(A2="yes",B2-B1,"")

Now use Sorting, Sort Col A for Decending Order, all the "yes" will
on top and

In Col C you will have results, use MAX(C1:C6) and MIN(C1:C6), then u
can paste special to values,

Again Sort Col B - Ascending order to get back original position.




On Aug 23, 8:42*pm, Kelvin wrote:
If there's a column with 100 "yes" or "no". I wanna calc the number of gaps
between each yes.

For example:
A1=yes
A2=no
A3=no
A4=yes
A5=no
A6=yes

Therefore, the output i wanna achieve is there is a maximum of 3 gaps
between the yes and no (between A1 to A4) while the minimum gaps will be 2
(A4 to A6)