Technically, the gaps would be 2 and 1. A2 & A3 = 2. A5 = 1.

These formulas need to be array entered** :

For the max gap:

=MAX(FREQUENCY(IF(A1:A6<"yes",ROW(A1:A6)),IF(A1:A 6="yes",ROW(A1:A6))))

For the min gap:

=MIN(IF(FREQUENCY(IF(A1:A6<"yes",ROW(A1:A6)),IF(A 1:A6="yes",ROW(A1:A6))),FREQUENCY(IF(A1:A6<"yes", ROW(A1:A6)),IF(A1:A6="yes",ROW(A1:A6)))))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

--
Biff
Microsoft Excel MVP


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