# 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

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### 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
5
Views
142
Replies
36
Views
1K
Replies
2
Views
177
Replies
7
Views
277
Replies
5
Views
117

1,140,941
Messages
5,703,292
Members
421,290
Latest member
java

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back