How to calculate gaps between events?
 

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)

How to calculate gaps between events?
 

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)

How to calculate gaps between events?
 

You can also write an "if" statement in the column to the right of the yes/no
column, as follows:

Let's say the yes/no clumn starts in cell A1 and continues down column A
if(b2="yes",1,0) and then in the column to the right of this column, a
summation to add up consecutive 1's:

if(and(c2=1,c1=1),d1+1,d1))

"Kelvin" wrote:

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

Then, you can do a value copy of this last column on the right and get the
series of maximum consecutive "runs" of yeses.

- Peter

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)

How to calculate gaps between events?
 

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)

How to calculate gaps between events?
 

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

How to calculate gaps between events?
 

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)