Home |
Search |
Today's Posts |
#1
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
"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
![]()
Posted to microsoft.public.excel.misc
|
|||
|
|||
![]()
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 |
Display Modes | |
|
|
![]() |
||||
Thread | Forum | |||
Dates - gaps between | Excel Discussion (Misc queries) | |||
Transpose Column With Gaps to Column With no gaps | Excel Discussion (Misc queries) | |||
How can I calculate events that happened at the same time? | Excel Discussion (Misc queries) | |||
Calculate % of events based on days worked | Excel Worksheet Functions | |||
Transpose Column With Gaps to Row With No Gaps? | Excel Discussion (Misc queries) |