| Home |
| Search |
| Today's Posts |
|
|
|
#1
|
|||
|
|||
Count the number of times 1 happened in a row
Hi Everyone. I'm trying to figure out how to do a formula for the number of
times 1 happened in a row starting from a certain date. For example, from A1 I have 1,1,1,0,0,1,1,1,1,0,0 - I would like to get the answer of 3 (as starting from A1 I have 3 "1s" in a row, the rest of the numbers aren't relevant). The max I want to be able to get is 11. -- Thanks! Stephen |
|
#2
|
|||
|
|||
|
Try this............
In L1 put 0 In M1 put =CONCATENATE(A1,B1,C1,D1,E1,F1,G1,H1,I1,J1,K1,L1) In N1 put =MID(FIND(0,M1,1)-1,1,99) Vaya con Dios, Chuck, CABGx3 "Stephen" wrote in message ... Hi Everyone. I'm trying to figure out how to do a formula for the number of times 1 happened in a row starting from a certain date. For example, from A1 I have 1,1,1,0,0,1,1,1,1,0,0 - I would like to get the answer of 3 (as starting from A1 I have 3 "1s" in a row, the rest of the numbers aren't relevant). The max I want to be able to get is 11. -- Thanks! Stephen |
|
#3
|
|||
|
|||
|
Thank you.
I was hoping to put the formula all in one cell as I have so many formulas all together. Do you know a way to do that? Thanks! "CLR" wrote: Try this............ In L1 put 0 In M1 put =CONCATENATE(A1,B1,C1,D1,E1,F1,G1,H1,I1,J1,K1,L1) In N1 put =MID(FIND(0,M1,1)-1,1,99) Vaya con Dios, Chuck, CABGx3 "Stephen" wrote in message ... Hi Everyone. I'm trying to figure out how to do a formula for the number of times 1 happened in a row starting from a certain date. For example, from A1 I have 1,1,1,0,0,1,1,1,1,0,0 - I would like to get the answer of 3 (as starting from A1 I have 3 "1s" in a row, the rest of the numbers aren't relevant). The max I want to be able to get is 11. -- Thanks! Stephen |
|
#4
|
|||
|
|||
|
=MID(FIND(0,CONCATENATE(A1,B1,C1,D1,E1,F1,G1,H1,I1 ,J1,K1,L1)&0,1)-1,1,99)
Vaya con Dios, Chuck, CABGx3 "Stephen" wrote in message ... Thank you. I was hoping to put the formula all in one cell as I have so many formulas all together. Do you know a way to do that? Thanks! "CLR" wrote: Try this............ In L1 put 0 In M1 put =CONCATENATE(A1,B1,C1,D1,E1,F1,G1,H1,I1,J1,K1,L1) In N1 put =MID(FIND(0,M1,1)-1,1,99) Vaya con Dios, Chuck, CABGx3 "Stephen" wrote in message ... Hi Everyone. I'm trying to figure out how to do a formula for the number of times 1 happened in a row starting from a certain date. For example, from A1 I have 1,1,1,0,0,1,1,1,1,0,0 - I would like to get the answer of 3 (as starting from A1 I have 3 "1s" in a row, the rest of the numbers aren't relevant). The max I want to be able to get is 11. -- Thanks! Stephen |
|
#5
|
|||
|
|||
|
If the range will always start with 1 and you want to count from the first 1
you can use =IF(COUNTIF(A1:A11,1)=COUNT(A1:A11),COUNT(A1:A11), MATCH(TRUE,A1:A11<1,0)-MATCH(1,A1:A11,0)) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "Stephen" wrote in message ... Hi Everyone. I'm trying to figure out how to do a formula for the number of times 1 happened in a row starting from a certain date. For example, from A1 I have 1,1,1,0,0,1,1,1,1,0,0 - I would like to get the answer of 3 (as starting from A1 I have 3 "1s" in a row, the rest of the numbers aren't relevant). The max I want to be able to get is 11. -- Thanks! Stephen |
|
#6
|
|||
|
|||
|
Thank you.
I tried this formula and it returned #N/A. Do you know why this could be? "Peo Sjoblom" wrote: If the range will always start with 1 and you want to count from the first 1 you can use =IF(COUNTIF(A1:A11,1)=COUNT(A1:A11),COUNT(A1:A11), MATCH(TRUE,A1:A11<1,0)-MATCH(1,A1:A11,0)) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "Stephen" wrote in message ... Hi Everyone. I'm trying to figure out how to do a formula for the number of times 1 happened in a row starting from a certain date. For example, from A1 I have 1,1,1,0,0,1,1,1,1,0,0 - I would like to get the answer of 3 (as starting from A1 I have 3 "1s" in a row, the rest of the numbers aren't relevant). The max I want to be able to get is 11. -- Thanks! Stephen |
|
#7
|
|||
|
|||
|
As I said, you need to enter it with ctrl + shift & enter
-- Regards, Peo Sjoblom "Stephen" wrote in message ... Thank you. I tried this formula and it returned #N/A. Do you know why this could be? "Peo Sjoblom" wrote: If the range will always start with 1 and you want to count from the first 1 you can use =IF(COUNTIF(A1:A11,1)=COUNT(A1:A11),COUNT(A1:A11), MATCH(TRUE,A1:A11<1,0)-MATCH(1,A1:A11,0)) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "Stephen" wrote in message ... Hi Everyone. I'm trying to figure out how to do a formula for the number of times 1 happened in a row starting from a certain date. For example, from A1 I have 1,1,1,0,0,1,1,1,1,0,0 - I would like to get the answer of 3 (as starting from A1 I have 3 "1s" in a row, the rest of the numbers aren't relevant). The max I want to be able to get is 11. -- Thanks! Stephen |
|
#8
|
|||
|
|||
|
As I said, it doesn't work.
"Peo Sjoblom" wrote: As I said, you need to enter it with ctrl + shift & enter -- Regards, Peo Sjoblom "Stephen" wrote in message ... Thank you. I tried this formula and it returned #N/A. Do you know why this could be? "Peo Sjoblom" wrote: If the range will always start with 1 and you want to count from the first 1 you can use =IF(COUNTIF(A1:A11,1)=COUNT(A1:A11),COUNT(A1:A11), MATCH(TRUE,A1:A11<1,0)-MATCH(1,A1:A11,0)) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "Stephen" wrote in message ... Hi Everyone. I'm trying to figure out how to do a formula for the number of times 1 happened in a row starting from a certain date. For example, from A1 I have 1,1,1,0,0,1,1,1,1,0,0 - I would like to get the answer of 3 (as starting from A1 I have 3 "1s" in a row, the rest of the numbers aren't relevant). The max I want to be able to get is 11. -- Thanks! Stephen |
|
#9
|
|||
|
|||
|
Maybe, assuming no interspersed empty cells:
B1: =MATCH(1,A1:A11,0) B2: =LOOKUP(9.99999999999999E+307,CHOOSE({1,2},COUNTIF (A1:A11,1),MATCH(0,INDEX(A1:A11,B1+1):A11,0)+B1-B1)) Stephen wrote: Thank you. I tried this formula and it returned #N/A. Do you know why this could be? "Peo Sjoblom" wrote: If the range will always start with 1 and you want to count from the first 1 you can use =IF(COUNTIF(A1:A11,1)=COUNT(A1:A11),COUNT(A1:A11 ),MATCH(TRUE,A1:A11<1,0)-MATCH(1,A1:A11,0)) entered with ctrl + shift & enter -- Regards, Peo Sjoblom "Stephen" wrote in message ... Hi Everyone. I'm trying to figure out how to do a formula for the number of times 1 happened in a row starting from a certain date. For example, from A1 I have 1,1,1,0,0,1,1,1,1,0,0 - I would like to get the answer of 3 (as starting from A1 I have 3 "1s" in a row, the rest of the numbers aren't relevant). The max I want to be able to get is 11. -- Thanks! Stephen |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How do I count the number of times a particular charcter ("." say) | Excel Discussion (Misc queries) | |||
| Number of times a number is shown | Excel Worksheet Functions | |||
| How do I count the times a number occurs in a given criteria? | Excel Discussion (Misc queries) | |||
| Count number to reach a cumulative value | Excel Worksheet Functions | |||
| Count number of shaded cells | Excel Discussion (Misc queries) | |||
Hybrid Mode
