# Using MIN but weeding out 0 values

#### henway

##### Board Regular
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!

### Excel Facts

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

#### HalfAce

##### MrExcel MVP
Code:
``{=MIN(IF(A1:A10>0,A1:A10))}``

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

#### Joe Was

##### MrExcel MVP
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

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

Regards,

#### Joe Was

##### MrExcel MVP
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!

Replies
25
Views
2K
Replies
8
Views
172
Replies
5
Views
47
Replies
5
Views
90
Replies
9
Views
57