Counting problem

D

daydreamin7

I am trying to find a way to count cells in a range, I want them to count
numbers greater than 0. But when I hit the first 0 (I have it on
randomization), I want it to stop counting... because the game is over for
the simulation I'm dong. Any ideas what I can do?
 
A

Ardus Petus

Do you have any numbers < 0 (negative)?
if not,
=IF(ISNA(MATCH(0,A2:A999,0),ROWS(A2:A999),MATCH(0,A2:A999))
will return the count of non-zero numbers before you hit 0 or end-of-list.

HTH
 
B

Bob Phillips

=IF(MIN(IF((A1:A20=0)*(A1:A20<>""),ROW(A1:A20)))=0,ROWS(A1:A20),MIN(IF((A1:A
20=0)*(A1:A20<>""),ROW(A1:A20))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
 
D

daydreamin7

This formula won't change when I hit F9 to randomize it. I need the formula
to work when I change the numbers.
--
~Laura~


Bob Phillips said:
=IF(MIN(IF((A1:A20=0)*(A1:A20<>""),ROW(A1:A20)))=0,ROWS(A1:A20),MIN(IF((A1:A
20=0)*(A1:A20<>""),ROW(A1:A20))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)
Click to expand...
 
D

daydreamin7

I don't have any negative numbers, but sometimes when I hit F9 to randomize,
it shows N/A and says it has a problem calculating the MATCH part of the
function. Any ideas?
--
~Laura~


Ardus Petus said:
Do you have any numbers < 0 (negative)?
if not,
=IF(ISNA(MATCH(0,A2:A999,0),ROWS(A2:A999),MATCH(0,A2:A999))
will return the count of non-zero numbers before you hit 0 or end-of-list.

HTH
Click to expand...
 
B

Bob Phillips

It changes fine for me. Did you array enter it?

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

daydreamin7 said:
This formula won't change when I hit F9 to randomize it. I need the formula
to work when I change the numbers.
Click to expand...
 
D

Domenic

Assuming that A2:A100 contains your numbers, try...

=IF(COUNTIF(A2:A100,0),COUNTIF(A2:INDEX(A2:A100,MATCH(0,A2:A100,0)),">0")
,COUNTIF(A2:A100,">0"))

Hope this helps!
 
D

daydreamin7

Yes! that did it! thank you so much, you just saved my operations management
grade! :)
 
Top