Sep 28 2016
04:42 AM
- last edited on
Jul 25 2018
09:21 AM
by
TechCommunityAP
Sep 28 2016
04:42 AM
- last edited on
Jul 25 2018
09:21 AM
by
TechCommunityAP
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
Sep 28 2016 05:10 AM - edited Sep 28 2016 05:11 AM
Alternative could be =MINIFS(A2:A10,A2:A10,">0")
Sep 28 2016 05:15 AM
Very cool,
but this function does not specifically appear, but only with OFFICE 365
Sep 28 2016 05:22 AM
Oct 07 2016 03:34 PM
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))
Nov 01 2016 06:25 AM - edited Nov 01 2016 06:31 AM
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.
Nov 01 2016 06:48 AM
Nov 09 2016 06:02 AM
I generaly use a array formula for that like this one
=MIN(IF(A2:A10>0;A2:A10))
Nov 22 2016 11:00 AM
I would usualy be lazy and add a new column with the formula =IF(A2=0,"",A2) then do a Min of that Column.