Home

Find the smallest value other than zero

Mohammed A Elrify
Occasional Contributor

Find the smallest value other than zero

Sometimes my question is the search for the smallest value, except for zero

The function min or small

But fail to it because it will come in short, a smallest value of zero

And we are forced to do the work of an array formula, such as in the picture

And the following suggestion

Argument optional functions

max min av

small large

To neglected zero.

It will be a very, very useful

Greetings to you

 

 

555555555.png

8 Replies
Highlighted

Re: Find the smallest value other than zero

Alternative could be =MINIFS(A2:A10,A2:A10,">0")

Re: Find the smallest value other than zero

Very cool,

but this function does not specifically appear, but only with OFFICE 365

Re: Find the smallest value other than zero

Yes. More exactly Excel 2016, not necessary from O365. Even more exactly Excel 2016, Excel Online, Excel for Android tablets, Excel Mobile, Excel for Android (https://support.office.com/en-us/article/MINIFS-function-6ca1ddaa-079b-4e74-80cc-72eef32e6599?ui=en-...).

On the other hand if new function appears when in Excel 2016 and later.

Re: Find the smallest value other than zero

A slightly easier variation of Mohammed's formula for users without MINIFS. Like Mohammed's formula, this one also needs to be array entered.

 

=MIN(IF(A2:A10*1=0,"",A2:A10))

 

Re: Find the smallest value other than zero

I usually use:

 

=AGGREGATE(15,6,1/(1/A2:A10),ROW(A1))

The trick is that division by zero becomes an error and it is ignored by the option 6 of the AGGREGATE() function.

The second division return the value of the other numbers.

No need of Ctr + Shift + Enter. 

 

AGGREGATE() is present on Excel 2010 and later versions.

Re: Find the smallest value other than zero

Great, all this discussion is perfectly illustrates what Excel is infinite as Universe

Re: Find the smallest value other than zero

I generaly use a array formula for that like this one

=MIN(IF(A2:A10>0;A2:A10))

Re: Find the smallest value other than zero

I would usualy be lazy and add a new column with the formula =IF(A2=0,"",A2) then do a Min of that Column.