I'm looking for a function (or macro) that can look at all cells in a single
column between variable row numbers--variable in that the row numbers are
dependent on information in a different column--and gives a one or zero
depending on whether there are non-zero numbers in the range (that the
function targets). Here is an illustration of what I am looking for:

Center passes raw numbers What I am looking for
1 0
1 0
1 2
1 2
1 0
1 0
1 0
1 2
1 2
1 2 1
2 0
2 2
2 2
2 2 1
3 0
3 0
3 0
3 0 0
4 0

The center passes always change by one, but the number of rows dedicated to
each number changes. The numbers in the 'raw number' column will always be
equal to 2. Any and all help would be much appreciated.

Thanks,
beecher

=IF(COUNTIF(A2:A3,A2)1,"",IF(AND(COUNTIF(A2:A3,A2 )=1,B2=0),0,1))


"beecher" wrote:

I'm looking for a function (or macro) that can look at all cells in a single
column between variable row numbers--variable in that the row numbers are
dependent on information in a different column--and gives a one or zero
depending on whether there are non-zero numbers in the range (that the
function targets). Here is an illustration of what I am looking for:

Center passes raw numbers What I am looking for
1 0
1 0
1 2
1 2
1 0
1 0
1 0
1 2
1 2
1 2 1
2 0
2 2
2 2
2 2 1
3 0
3 0
3 0
3 0 0
4 0

The center passes always change by one, but the number of rows dedicated to
each number changes. The numbers in the 'raw number' column will always be
equal to 2. Any and all help would be much appreciated.

Thanks,
beecher

I put this in C2:

=IF(COUNTIF($A$2:A2,A2)<COUNTIF(A:A,A2),"",
--(SUMPRODUCT(--($A$2:$A$999=A2),--($B$2:$B$999<0))0))

and dragged down the column.

Adjust those ranges (a2:a999 and b2:b999) to match--but don't use the whole
column.

beecher wrote:

I'm looking for a function (or macro) that can look at all cells in a single
column between variable row numbers--variable in that the row numbers are
dependent on information in a different column--and gives a one or zero
depending on whether there are non-zero numbers in the range (that the
function targets). Here is an illustration of what I am looking for:

Center passes raw numbers What I am looking for
1 0
1 0
1 2
1 2
1 0
1 0
1 0
1 2
1 2
1 2 1
2 0
2 2
2 2
2 2 1
3 0
3 0
3 0
3 0 0
4 0

The center passes always change by one, but the number of rows dedicated to
each number changes. The numbers in the 'raw number' column will always be
equal to 2. Any and all help would be much appreciated.

Thanks,
beecher

--

Dave Peterson