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.
--
Louis

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

--
Don Guillett
SalesAid Software

"Louis" wrote in message
...
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.
--
Louis

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

"Louis" wrote in message
...
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.
--
Louis

Try this:

I'm assuming there are no negative numbers.

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

Biff

"Louis" wrote in message
...
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.
--
Louis

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)))



Biff wrote:

Try this:

I'm assuming there are no negative numbers.

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

Biff

"Louis" wrote in message
...
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.
--
Louis

--

Dave Peterson

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,
--
Louis


"Dave Peterson" wrote:

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)))



Biff wrote:

Try this:

I'm assuming there are no negative numbers.

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

Biff

"Louis" wrote in message
...
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.
--
Louis

--

Dave Peterson