Calculate Minimum in excel excluding zero

Excel Minimum excluding zero

Calculate minimum using Min Function excluding zeros in Excel

Excel has a built in formula/function that makes finding the minimum value in a range of cells easy. If we assume your numbers are in A1:A100 you would simply use the MIN formula like shown below;
=MIN(A1:A100)

Now if we have a dataset as show in the image-:

Now if I were to find the minimum price bid using Min function , the answer you would get is 0 to get the correct answer we have three ways one using small function,Array formula and other using filters and subtotals

Using Small Function

By far the most efficient method is to use the SMALL and countif as shown below;

SMALL Returns the k-th smallest value in a data set. Lets say the array is in column A

=SMALL(A1:A100,COUNTIF($A$1:$A$100,0)+1)

Where the countif is counting the zeros in the range (+1) and is used to tell SMALL to return the k-th smallest value.

Using Array Function

Next best method is to use array formulas . Now suppose your array is in column A , the formula you would use is

=MIN(IF($A$1:$A$100 >0,$A$1:$A$100))

As these are array formulas they MUST be entered via Ctrl+Shift+Enter. After this you will the answer and curly brackets next to the formula

Using Filters and Subtotal

Next best method is to use filters and subtotal function . Now suppose your array is in column A , the formula filter values (Alt + D+F+F) and remove 0 from the set , now use subtotal function 

=SUBTOTAL(5,$A$1:$A$100)

This will give you the minimum value in the filtered array , you could also use this function for many other operations on a filtered array

Hope this helps