Kelvin said:
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 -----