Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting blocks of 1's
Anyone have any suggestions.
I'm trying to count batches of 1's in a column from C4 to C512. Once that is done I can then move on to the other columns which must be counted separately. The columns go from C to DH. I need to count the blocks going down each column, i.e. 111 11 1 1111 1111 11111 would =6. 11111111 1111=2 The sheet column A is date (month & year) B is day. It must have 2 years going down for each working day of the 2 years, 512 I can't go across the sheet because of the limitations of cells going across. I've tried =SUMPRODUCT((C1:C511=1)*(C2:C512<1)) & =SUMPRODUCT(--(C1:C511=1),--(C2:C512<1)). The second formula seems to add 1 to the count. The first is fine unless there is only 1 in the column when it returns a 0. Anybody got a fix, I'd be grateful. Bryan. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting blocks of 1's
You mean that within your cells you could have one or more digits, but
you are only interested in counting those cells which only have 1's in them, no matter how many times? Have a look at the SUBSTITUTE function in Excel Help - substitute "" for a "1" and test to see if the length is zero after applying this. Hope this helps. Pete On Feb 17, 8:21*pm, "Bryan De-Lara" wrote: Anyone have any suggestions. I'm trying to count batches of 1's in a column from C4 to C512. Once that is done I can then move on to the other columns which must be counted separately. The columns go from C to DH. I need to count the blocks going down each column, i.e. 111 *11 *1 *1111 1111 * *11111 would =6. 11111111 * * 1111=2 The sheet column A is date (month & year) B is day. It must have 2 years going down for each working day of the 2 years, 512 I can't go across the sheet because of the limitations of cells going across. I've tried =SUMPRODUCT((C1:C511=1)*(C2:C512<1)) & =SUMPRODUCT(--(C1:C511=1),--(C2:C512<1)). The second formula seems to add 1 to the count. The first is fine unless there is only 1 in the column when it returns a 0. Anybody got a fix, I'd be grateful. Bryan. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting blocks of 1's
No Pete, the cells will either be blank or 1, If 1 has a blank on either
side it is 1 block. If two or more cells together have 1 and a blank cell that is another block. So you could have all 512 cells with a 1 and be 1 block, or 25 blocks if blank cells are between. It's the amount of blocks I need. As it's easy enough to count the individual 1's with =sum that's not the problem. I need to know so that I can calculate by converting into points. Hope you can help. The substitute wasn't much help. Thanks. Bryan. "Pete_UK" wrote in message ... You mean that within your cells you could have one or more digits, but you are only interested in counting those cells which only have 1's in them, no matter how many times? Have a look at the SUBSTITUTE function in Excel Help - substitute "" for a "1" and test to see if the length is zero after applying this. Hope this helps. Pete On Feb 17, 8:21 pm, "Bryan De-Lara" wrote: Anyone have any suggestions. I'm trying to count batches of 1's in a column from C4 to C512. Once that is done I can then move on to the other columns which must be counted separately. The columns go from C to DH. I need to count the blocks going down each column, i.e. 111 11 1 1111 1111 11111 would =6. 11111111 1111=2 The sheet column A is date (month & year) B is day. It must have 2 years going down for each working day of the 2 years, 512 I can't go across the sheet because of the limitations of cells going across. I've tried =SUMPRODUCT((C1:C511=1)*(C2:C512<1)) & =SUMPRODUCT(--(C1:C511=1),--(C2:C512<1)). The second formula seems to add 1 to the count. The first is fine unless there is only 1 in the column when it returns a 0. Anybody got a fix, I'd be grateful. Bryan. |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting blocks of 1's
=SUMPRODUCT(--ISNUMBER(FIND(1,C2:C512)))
"Bryan De-Lara" wrote: Anyone have any suggestions. I'm trying to count batches of 1's in a column from C4 to C512. Once that is done I can then move on to the other columns which must be counted separately. The columns go from C to DH. I need to count the blocks going down each column, i.e. 111 11 1 1111 1111 11111 would =6. 11111111 1111=2 The sheet column A is date (month & year) B is day. It must have 2 years going down for each working day of the 2 years, 512 I can't go across the sheet because of the limitations of cells going across. I've tried =SUMPRODUCT((C1:C511=1)*(C2:C512<1)) & =SUMPRODUCT(--(C1:C511=1),--(C2:C512<1)). The second formula seems to add 1 to the count. The first is fine unless there is only 1 in the column when it returns a 0. Anybody got a fix, I'd be grateful. Bryan. |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting blocks of 1's
Thanks Teethless mama, that counts the 1's in the row, i.e. 11 11 111=7
which I need to read 3 for 3 blocks of 1's. This of course is going down, not across. Bryan. "Teethless mama" wrote in message ... =SUMPRODUCT(--ISNUMBER(FIND(1,C2:C512))) "Bryan De-Lara" wrote: Anyone have any suggestions. I'm trying to count batches of 1's in a column from C4 to C512. Once that is done I can then move on to the other columns which must be counted separately. The columns go from C to DH. I need to count the blocks going down each column, i.e. 111 11 1 1111 1111 11111 would =6. 11111111 1111=2 The sheet column A is date (month & year) B is day. It must have 2 years going down for each working day of the 2 years, 512 I can't go across the sheet because of the limitations of cells going across. I've tried =SUMPRODUCT((C1:C511=1)*(C2:C512<1)) & =SUMPRODUCT(--(C1:C511=1),--(C2:C512<1)). The second formula seems to add 1 to the count. The first is fine unless there is only 1 in the column when it returns a 0. Anybody got a fix, I'd be grateful. Bryan. |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting blocks of 1's
Try this array formula** :
=COUNT(1/FREQUENCY(IF(A1:A18=1,ROW(A1:A18)),IF(A1:A18<1,RO W(A1:A18)))) screencap: http://img208.imageshack.us/img208/5...upsof1smm0.jpg ** array formulas need to be entered using the key combination of CTRL,SHIFT,ENTER (not just ENTER) -- Biff Microsoft Excel MVP "Bryan De-Lara" wrote in message ... Anyone have any suggestions. I'm trying to count batches of 1's in a column from C4 to C512. Once that is done I can then move on to the other columns which must be counted separately. The columns go from C to DH. I need to count the blocks going down each column, i.e. 111 11 1 1111 1111 11111 would =6. 11111111 1111=2 The sheet column A is date (month & year) B is day. It must have 2 years going down for each working day of the 2 years, 512 I can't go across the sheet because of the limitations of cells going across. I've tried =SUMPRODUCT((C1:C511=1)*(C2:C512<1)) & =SUMPRODUCT(--(C1:C511=1),--(C2:C512<1)). The second formula seems to add 1 to the count. The first is fine unless there is only 1 in the column when it returns a 0. Anybody got a fix, I'd be grateful. Bryan. |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting blocks of 1's
=SUMPRODUCT(--ISNUMBER(FIND(1,1:1)))
"Bryan De-Lara" wrote: Thanks Teethless mama, that counts the 1's in the row, i.e. 11 11 111=7 which I need to read 3 for 3 blocks of 1's. This of course is going down, not across. Bryan. "Teethless mama" wrote in message ... =SUMPRODUCT(--ISNUMBER(FIND(1,C2:C512))) "Bryan De-Lara" wrote: Anyone have any suggestions. I'm trying to count batches of 1's in a column from C4 to C512. Once that is done I can then move on to the other columns which must be counted separately. The columns go from C to DH. I need to count the blocks going down each column, i.e. 111 11 1 1111 1111 11111 would =6. 11111111 1111=2 The sheet column A is date (month & year) B is day. It must have 2 years going down for each working day of the 2 years, 512 I can't go across the sheet because of the limitations of cells going across. I've tried =SUMPRODUCT((C1:C511=1)*(C2:C512<1)) & =SUMPRODUCT(--(C1:C511=1),--(C2:C512<1)). The second formula seems to add 1 to the count. The first is fine unless there is only 1 in the column when it returns a 0. Anybody got a fix, I'd be grateful. Bryan. |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting blocks of 1's
The solution has already been given in microsoft.public.excel.
Please don't post the same question in multiple NGs - especially after you've already been given the answer!!! Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Bryan De-Lara" wrote in message ... Anyone have any suggestions. I'm trying to count batches of 1's in a column from C4 to C512. Once that is done I can then move on to the other columns which must be counted separately. The columns go from C to DH. I need to count the blocks going down each column, i.e. 111 11 1 1111 1111 11111 would =6. 11111111 1111=2 The sheet column A is date (month & year) B is day. It must have 2 years going down for each working day of the 2 years, 512 I can't go across the sheet because of the limitations of cells going across. I've tried =SUMPRODUCT((C1:C511=1)*(C2:C512<1)) & =SUMPRODUCT(--(C1:C511=1),--(C2:C512<1)). The second formula seems to add 1 to the count. The first is fine unless there is only 1 in the column when it returns a 0. Anybody got a fix, I'd be grateful. Bryan. |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting blocks of 1's
Okay thanks macropod, but none worked, either it adds one or is a circular
reference. Thanks any way. I'll give up on it. Maybe I'm not explaining it properly. Bryan. "macropod" wrote in message ... The solution has already been given in microsoft.public.excel. Please don't post the same question in multiple NGs - especially after you've already been given the answer!!! Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Bryan De-Lara" wrote in message ... Anyone have any suggestions. I'm trying to count batches of 1's in a column from C4 to C512. Once that is done I can then move on to the other columns which must be counted separately. The columns go from C to DH. I need to count the blocks going down each column, i.e. 111 11 1 1111 1111 11111 would =6. 11111111 1111=2 The sheet column A is date (month & year) B is day. It must have 2 years going down for each working day of the 2 years, 512 I can't go across the sheet because of the limitations of cells going across. I've tried =SUMPRODUCT((C1:C511=1)*(C2:C512<1)) & =SUMPRODUCT(--(C1:C511=1),--(C2:C512<1)). The second formula seems to add 1 to the count. The first is fine unless there is only 1 in the column when it returns a 0. Anybody got a fix, I'd be grateful. Bryan. |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting blocks of 1's
You are explaining correctly.
You are not reading properly the replies you received from macropod and myself. Did you try inserting a blank row at top and entering =SUMPRODUCT(--(C2:C512=1),--(C3:C513<1)) in C1 and dragging across? Gord Dibben MS Excel MVP On Sun, 17 Feb 2008 23:04:24 -0000, "Bryan De-Lara" wrote: Okay thanks macropod, but none worked, either it adds one or is a circular reference. Thanks any way. I'll give up on it. Maybe I'm not explaining it properly. Bryan. "macropod" wrote in message ... The solution has already been given in microsoft.public.excel. Please don't post the same question in multiple NGs - especially after you've already been given the answer!!! Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Bryan De-Lara" wrote in message ... Anyone have any suggestions. I'm trying to count batches of 1's in a column from C4 to C512. Once that is done I can then move on to the other columns which must be counted separately. The columns go from C to DH. I need to count the blocks going down each column, i.e. 111 11 1 1111 1111 11111 would =6. 11111111 1111=2 The sheet column A is date (month & year) B is day. It must have 2 years going down for each working day of the 2 years, 512 I can't go across the sheet because of the limitations of cells going across. I've tried =SUMPRODUCT((C1:C511=1)*(C2:C512<1)) & =SUMPRODUCT(--(C1:C511=1),--(C2:C512<1)). The second formula seems to add 1 to the count. The first is fine unless there is only 1 in the column when it returns a 0. Anybody got a fix, I'd be grateful. Bryan. |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting blocks of 1's
Yes macropod I did, but in the next few rows when it is dragged across say
D1,E1,F1,G1,H1,I1,J1,K1,L1,M1,N1, (10 rows) it adds 1 to the count. I did add at the end of the formula -1 to make good, but then it reports -1 if there was no 1's entered. When a few rows are entered with 1's the rest behind change to 1 with no data entered. Maybe I am thick, I didn't think so, but I think this proved it. Anyway, I'd like to thank everyone who tried to help me, and put up with me, very kind indeed. I won't bother anyone anymore. Bryan. "Gord Dibben" <gorddibbATshawDOTca wrote in message ... You are explaining correctly. You are not reading properly the replies you received from macropod and myself. Did you try inserting a blank row at top and entering =SUMPRODUCT(--(C2:C512=1),--(C3:C513<1)) in C1 and dragging across? Gord Dibben MS Excel MVP On Sun, 17 Feb 2008 23:04:24 -0000, "Bryan De-Lara" wrote: Okay thanks macropod, but none worked, either it adds one or is a circular reference. Thanks any way. I'll give up on it. Maybe I'm not explaining it properly. Bryan. "macropod" wrote in message .. . The solution has already been given in microsoft.public.excel. Please don't post the same question in multiple NGs - especially after you've already been given the answer!!! Cheers -- macropod [MVP - Microsoft Word] ------------------------- "Bryan De-Lara" wrote in message ... Anyone have any suggestions. I'm trying to count batches of 1's in a column from C4 to C512. Once that is done I can then move on to the other columns which must be counted separately. The columns go from C to DH. I need to count the blocks going down each column, i.e. 111 11 1 1111 1111 11111 would =6. 11111111 1111=2 The sheet column A is date (month & year) B is day. It must have 2 years going down for each working day of the 2 years, 512 I can't go across the sheet because of the limitations of cells going across. I've tried =SUMPRODUCT((C1:C511=1)*(C2:C512<1)) & =SUMPRODUCT(--(C1:C511=1),--(C2:C512<1)). The second formula seems to add 1 to the count. The first is fine unless there is only 1 in the column when it returns a 0. Anybody got a fix, I'd be grateful. Bryan. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
blocks of data repeating | New Users to Excel | |||
How do I create criteria blocks | Excel Discussion (Misc queries) | |||
averaging particular blocks of data | Excel Discussion (Misc queries) | |||
Copy Blocks Of Data | Excel Discussion (Misc queries) | |||
print in blocks in excel | Excel Discussion (Misc queries) |