|
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 |
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 |
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 |
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 |
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 |
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 |
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 |
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 |
=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 |
| All times are GMT +1. The time now is 04:31 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com