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...
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))
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.
- Great, all this discussion is perfectly illustrates what Excel is infinite as Universe
- 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-US&rs=en-US&ad=US).
On the other hand if new function appears when in Excel 2016 and later.
