Using MIN but weeding out 0 values

henway

Board Regular
Joined
Jul 19, 2006
Messages
85
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!
 

Some videos you may like

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
Joined
Apr 6, 2003
Messages
9,453
Assume your range was A1:A10:
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
Joined
Feb 19, 2002
Messages
7,539
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
Joined
Jul 19, 2006
Messages
85
Thanks to all!
The CSE method wil work the best for me!

Regards,
 

Joe Was

MrExcel MVP
Joined
Feb 19, 2002
Messages
7,539
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,113,862
Messages
5,544,726
Members
410,630
Latest member
JFORTH97
Top