finding minimum value excluding zero

[bookman3]

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

 

[Dave Peterson]

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:A25>0,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.)

 

[Dave O]

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(a1>0,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

 

[JMB]

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

 

[T. Valko]

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:A10>0,A1:A10))

Biff

 

[bookman3]

Thanks a lot
--
bookman

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

 

[JMB]

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.