Hi Kelvin,

here is possible solution :
at b1 type =1
at b2 type =if(a2=b1,b1+1,1) and copy down the formula as per your
requirement.

at c1 type ="Maximum is "&max(your range)
at c2 type ="Minimum is &min(your range)

hope this works

clicke yes below, if it works for you

"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)