Forum Discussion
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
8 Replies
I would usualy be lazy and add a new column with the formula =IF(A2=0,"",A2) then do a Min of that Column.
I generaly use a array formula for that like this one
=MIN(IF(A2:A10>0;A2:A10))
Alternative could be =MINIFS(A2:A10,A2:A10,">0")
Very cool,
but this function does not specifically appear, but only with OFFICE 365
- JoeMcDaid
Microsoft
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))
