"Kelvin" wrote:
If there's a column with 100 "yes" or "no". I wanna calc the
number of gaps between each yes.

Long ago, someone suggested a method that worked without helper cells.
Sigh, I cannot find that solution now.

So here are solutions that use helper cells in column B.


For example:
A1=yes
A2=no
A3=no
A4=yes
[....]
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)

You may define the "gap" count anyway you wish.

But what about when A1="no"?

1. If the first "gap" would be 4, then:

B1: =if(A1="yes","",1)
B2: =if(A1="yes",1,n(B1)+1)

Copy B2 down through B100. Then:

max gap: =max(B2:B100)
min gap: =min(IF(A2:A100="yes",B2:B100))

2. If you want to ignore everything until the first "yes", then:

B1: empty
B2: =if(A1="yes",1,if(B1="","",B1+1))

Copy B2 down through B100. Then:

max gap: =max(B2:B100)
min gap: =min(if((A2:A20="yes")*(B2:B20<""),B2:B20))

Note that in both cases, the "min gap" formula is an array formula. Commit
with ctrl+shift+Enter instead of the usual Enter. If you forget, press F2,
then press ctrl+shift+Enter.


----- original message -----

"Kelvin" wrote in message
...
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)