Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 13
Default 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)
  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 41
Default 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)

  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 349
Default 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)

  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 15,768
Default 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)



  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 2,059
Default 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)




  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 747
Default 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)


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Dates - gaps between gecko123 Excel Discussion (Misc queries) 3 August 19th 08 07:59 PM
Transpose Column With Gaps to Column With no gaps Vincent Excel Discussion (Misc queries) 9 October 1st 07 02:50 PM
How can I calculate events that happened at the same time? Stuboy28 Excel Discussion (Misc queries) 2 February 28th 07 10:06 PM
Calculate % of events based on days worked Karen Excel Worksheet Functions 1 January 23rd 07 05:25 AM
Transpose Column With Gaps to Row With No Gaps? SteveC Excel Discussion (Misc queries) 5 June 18th 05 02:01 AM


All times are GMT +1. The time now is 06:14 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"