SMALL function if > 0

[Guest]

How do I create a function that looks at a column of numbers and give show
the smallest number that is greater than zero? I tried a nested if function
but it doesn't give me the correct answer.

 

[Don Guillett]

array enter with ctrl+shift+enter
=MIN(IF(A1:A100>0,A1:A100))

 

[Tieske]

I would add a helper-column. Say the values are in column A, then cell B1
should be:
=if(A1<=0;99999999;A1)
Copy this formula to the whole column B

Getting the smallest number greater than 0 would now be
=min(B:B)

Not beautifull, but it works

Tieske

 

[Biff]

Try this:

I'm assuming there are no negative numbers.

=SMALL(A1:A10,1+COUNTIF(A1:A10,0))

Biff

 

[Dave Peterson]

And building on Biff's formula:

If there are negative numbers:
=SMALL(A1:A10,1+COUNTIF(A1:A10,"<="&0))

and in case there are no positive numbers:
=IF(COUNTIF(A1:A10,">"&0)=0,"No numbers larger than 0",
SMALL(A1:A10,1+COUNTIF(A1:A10,"<="&0)))

 

[Guest]

Thanks everyone. There are negative numbers to I will use that formula. The
helper column I should have thought up myself (but it never occurred to me);
but I never would have been able to produce the formula with the COUNTIF
without your help, even though I tried myself (unsuccessfully) before I
posted to the users group.
Thanks again,