+ Reply to Thread
Results 1 to 6 of 6

Find smallest number that fits a criteria

  1. #1
    Registered User
    Join Date
    03-10-2014
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    70

    Find smallest number that fits a criteria

    Hello everyone, but I've been a bit stumped on this probably-simple problem.

    I'm looking to find the smallest number in a column that fits a certain criteria, and I'm not sure where to put the if function, or if I can use some other 'qualifiers'.

    I was thinking something along the lines of If(E:E<>2, Small(E:E, ROW()-1),____) but I'm not sure what the "false" argument for the if function would be in the blanks. I don't want there to be one.

    Where E:E is the range I'm looking in, and 'not equal to 2' is the criteria I'm looking to have filled.

    So, "Find the smallest number in E:E that does not equal 2" would be the wording.

  2. #2
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Find smallest number that fits a criteria

    Try this array formula

    =SMALL(IF(E:E<>2,E:E),1)

    confirmed with CTRL+SHIFT+ENTER

    You can leave FALSE argument blank - the FALSE values returned will be ignored by SMALL function

    replace 1 at the end with any number for nth smallest
    Audere est facere

  3. #3
    Registered User
    Join Date
    03-10-2014
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    70

    Re: Find smallest number that fits a criteria

    That would be perfect, but I forgot to mention that I was using the Small() function because I also need the 2nd smallest number that fits the criteria, 3rd, and so on. It looks like using Min will always return the very smallest.

  4. #4
    Forum Expert daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,675

    Re: Find smallest number that fits a criteria

    OK, I realised that before you posted and edited my suggestion appropriately - it's effectively the same syntax with SMALL or MIN

  5. #5
    Registered User
    Join Date
    03-10-2014
    Location
    California
    MS-Off Ver
    Excel 2007
    Posts
    70

    Re: Find smallest number that fits a criteria

    Ah, perfect! Thank you! I figured there was a simple way of showing criteria. Mental note made.

  6. #6
    Forum Contributor
    Join Date
    01-30-2011
    Location
    Vancouver, Canada
    MS-Off Ver
    Excel 2010
    Posts
    604

    Re: Find smallest number that fits a criteria

    =if(small(e:e,1)=2,small(e:e,2),small(e:e,1))
    Attached Files Attached Files

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] find smallest Number in a col
    By bnwash in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-12-2013, 10:26 PM
  2. [SOLVED] How to find the smallest number for each category
    By BNCOXUK in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-07-2013, 01:13 PM
  3. Replies: 1
    Last Post: 04-20-2010, 02:34 PM
  4. find the smallest number greater than Zero
    By carsto in forum Excel General
    Replies: 3
    Last Post: 10-06-2006, 04:25 PM
  5. find smallest number not equal to 0
    By dave in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-20-2005, 06:06 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1