Using MIN but weeding out 0 values

[henway]

I have 2 columns of prices. Some are filled in and some have never been quoted and are thus "0".

How do I create a formula that shows the smallest number except for 0?

Thanks!

 

[lenze]

See here for an idea
http://www.mrexcel.com/board2/viewtopic.php?t=234869&highlight=min

lenze

 

[HalfAce]

Assume your range was A1:A10:

{=MIN(IF(A1:A10>0,A1:A10))}

Must be entered as an array formula - with Ctrl+Shift+Enter, not just Enter.

 

[Joe Was]

If you will never be without a zero value then:

=SMALL(A1:A9,2)

Which picks the smallest number not = to 0 if zero is the smallest value in the range. [AKA the 2nd smallest number, zero being the 1st smallest number.] So, you cannot have negative numbers or these will be smaller than zero, giving a chance that zero now becomes the second smallest number!

So, your list must have at least one zero and cannot have any negative numbers.

 

[henway]

Thanks to all!
The CSE method wil work the best for me!

Regards,

 

[Joe Was]

You should note that the above link's formula [shown here] will also only work with positive numbers:

=SMALL(AJ2:AP2,COUNTIF(AJ2:AP2,0)+1)

If you have negative number(s) you can return a value of 0, if it is the next higher value!