Hi

I have a list of values including zeros. How can I find the minimum value in
the list that is greater than zero.

Regards
--
bookman

One way:
=IF(COUNTIF(A1:A25,""&0)=0,"No numbers greater than 0",
SMALL(A1:A25,COUNTIF(A1:A25,"<="&0)+1))

Another:
=IF(COUNTIF(A1:A25,""&0)=0,"No numbers greater than 0",
MIN(IF(A1:A250,A1:A25)))

The second formula is an array formula. Hit ctrl-shift-enter instead of enter.
If you do it correctly, excel will wrap curly brackets {} around your formula.
(don't type them yourself.)


bookman3 wrote:

Hi

I have a list of values including zeros. How can I find the minimum value in
the list that is greater than zero.

Regards
--
bookman

--

Dave Peterson

The best way I can think of to do this would be to insert a "helper"
column next to the list of values, then write a formula like this one
=IF(a10,a1,"")
....and then copy/paste that formula for each value in the list. This
will return only the values that are greater than zero; you can then
use the MIN() function over that range to find the smallest non-zero
value.

Dave O

Assuming the data is in A1:A10, try:

=MIN(IF(A1:A10<0,A1:A10))
array entered using Cntrl+Shift+Enter

or
=SMALL(A1:A10,IF(COUNTIF(A1:A10,"<0"),1,COUNTIF(A1 :A10,0)+1))


"bookman3" wrote:

Hi

I have a list of values including zeros. How can I find the minimum value in
the list that is greater than zero.

Regards
--
bookman

Try one of these. both formulas are array formulas and need to be entered
using the key combination of CTRL,SHIFT,ENTER (not just ENTER):

If all the values are positive and there are no TEXT values in the range:

=MIN(IF(A1:A10,A1:A10))

Another way that excludes TEXT values and negative numbers:

=MIN(IF(A1:A100,A1:A10))

Biff

"bookman3" wrote in message
...
Hi

I have a list of values including zeros. How can I find the minimum value
in
the list that is greater than zero.

Regards
--
bookman

Thanks a lot
--
bookman


"JMB" wrote:

Assuming the data is in A1:A10, try:

=MIN(IF(A1:A10<0,A1:A10))
array entered using Cntrl+Shift+Enter

or
=SMALL(A1:A10,IF(COUNTIF(A1:A10,"<0"),1,COUNTIF(A1 :A10,0)+1))


"bookman3" wrote:

Hi

I have a list of values including zeros. How can I find the minimum value in
the list that is greater than zero.

Regards
--
bookman

You should review Dave's and Biff's suggestion. I misread your post and
thought you wanted the smallest *nonzero* value instead of the smallest value
*greater* than zero. If there are negative values, you will not get the
results you indicate you want.


"bookman3" wrote:

Thanks a lot
--
bookman


"JMB" wrote:

Assuming the data is in A1:A10, try:

=MIN(IF(A1:A10<0,A1:A10))
array entered using Cntrl+Shift+Enter

or
=SMALL(A1:A10,IF(COUNTIF(A1:A10,"<0"),1,COUNTIF(A1 :A10,0)+1))


"bookman3" wrote:

Hi

I have a list of values including zeros. How can I find the minimum value in
the list that is greater than zero.

Regards
--
bookman