turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- 120K Members
- 1,462 Online
- 30K Conversations

- Home
- :
- Excel
- :
- Formulas and Functions
- :
- Re: Find the smallest value other than zero

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Subscribe to RSS Feed
- Mark Conversation as New
- Mark Conversation as Read
- Pin this Conversation for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-28-2016 04:42 AM - edited 09-28-2016 04:45 AM

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

Labels:

8 Replies

Highlighted
##
##### Re: Find the smallest value other than zero

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-28-2016 05:10 AM - edited 09-28-2016 05:11 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-28-2016 05:15 AM

Very cool,

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-28-2016 05:22 AM

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.

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

10-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))

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-01-2016 06:25 AM - edited 11-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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-01-2016 06:48 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-09-2016 06:02 AM

I generaly use a array formula for that like this one

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-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.

Related Conversations

Help Us Understand Office 365 User Needs

Sharon Liu
in
FastTrack for Microsoft 365
on
06-05-2017
2,185
Views

5 Likes

7 Replies

Outlook shared Calendar > All day events spans two days

Mike Jansen
in
Outlook
on
07-19-2017
696
Views

0 Likes

4 Replies

Excel Shortcut Keys ctrl+f and ctrl+h now gives the same function?

Nino Angelo Junsay
in
Excel
on
09-25-2017
3,783
Views

1 Likes

22 Replies

Techniques Around Helping Users Decide: When To Use What In Office 365

Richard Harbridge
in
Driving Adoption
on
07-15-2016
4,116
Views

23 Likes

21 Replies

What are potential candidates for an out of the box Intranet solutions based on SPOL

Eric Deferm
in
Driving Adoption
on
11-04-2016
399
Views

2 Likes

3 Replies

Share

Popular

Learning Resources

Programs

Values

- Contact us
- Privacy & cookies
- Terms of use
- Trademarks
- About our ads
- © 2017 Microsoft