Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
array question
Hi all...I hope someone can help me here, I have a single array column just
over 100 long which is filled with noughts and ones and I would like to count how many noughts there are between each number of ones. The answer could be EG 5,8,3,9,2,4 which tells me there is six gaps and the number of noughts in each gap. thanks for any help Len |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
array question
Try this:
Assume the data starts in cell A1. Enter this formula in B1: =IF(AND(A1=0,A2=1),1,"") Enter this formula in B2 and copy down to the end of data in column A. I'm assuming there is an empty cell at the end of data in column A. =IF(A2=1,"",IF(OR(A3=1,A3=""),COUNTIF(A$1:A2,0)-SUM(B$1:B1),"")) -- Biff Microsoft Excel MVP "Len Case" wrote in message ... Hi all...I hope someone can help me here, I have a single array column just over 100 long which is filled with noughts and ones and I would like to count how many noughts there are between each number of ones. The answer could be EG 5,8,3,9,2,4 which tells me there is six gaps and the number of noughts in each gap. thanks for any help Len |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
array question
Thankyou very much it did the trick great
thanks from Len "T. Valko" wrote in message ... Try this: Assume the data starts in cell A1. Enter this formula in B1: =IF(AND(A1=0,A2=1),1,"") Enter this formula in B2 and copy down to the end of data in column A. I'm assuming there is an empty cell at the end of data in column A. =IF(A2=1,"",IF(OR(A3=1,A3=""),COUNTIF(A$1:A2,0)-SUM(B$1:B1),"")) -- Biff Microsoft Excel MVP "Len Case" wrote in message ... Hi all...I hope someone can help me here, I have a single array column just over 100 long which is filled with noughts and ones and I would like to count how many noughts there are between each number of ones. The answer could be EG 5,8,3,9,2,4 which tells me there is six gaps and the number of noughts in each gap. thanks for any help Len |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
array question
You're welcome. Thanks for the feedback!
-- Biff Microsoft Excel MVP "Len Case" wrote in message ... Thankyou very much it did the trick great thanks from Len "T. Valko" wrote in message ... Try this: Assume the data starts in cell A1. Enter this formula in B1: =IF(AND(A1=0,A2=1),1,"") Enter this formula in B2 and copy down to the end of data in column A. I'm assuming there is an empty cell at the end of data in column A. =IF(A2=1,"",IF(OR(A3=1,A3=""),COUNTIF(A$1:A2,0)-SUM(B$1:B1),"")) -- Biff Microsoft Excel MVP "Len Case" wrote in message ... Hi all...I hope someone can help me here, I have a single array column just over 100 long which is filled with noughts and ones and I would like to count how many noughts there are between each number of ones. The answer could be EG 5,8,3,9,2,4 which tells me there is six gaps and the number of noughts in each gap. thanks for any help Len |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Index array question | Excel Worksheet Functions | |||
another array question | Excel Worksheet Functions | |||
Array Question | Excel Discussion (Misc queries) | |||
Matrix/array question | Excel Discussion (Misc queries) | |||
Array Question | Excel Worksheet Functions |