+ Reply to Thread
Results 1 to 4 of 4

Finding the 2nd smallest number

  1. #1
    Registered User
    Join Date
    01-07-2008
    Posts
    19

    Finding the 2nd smallest number

    I want to know the 2nd lowest number in the following list.
    I have tried using SMALL(X:X,2) but it returns 14.00 because there a two number 14's. The answer I want is 14.50.

    Many thanks

    14.00
    14.00
    14.50
    14.50
    15.00
    16.00
    16.00
    16.00
    16.00
    16.00
    16.00
    16.00
    16.50
    16.50
    16.50
    16.50
    17.00
    17.00
    17.00
    17.00
    17.00
    17.50
    19.50
    20.00

  2. #2
    Forum Expert shg's Avatar
    Join Date
    06-20-2007
    Location
    The Great State of Texas
    MS-Off Ver
    2010, 2019
    Posts
    40,689
    Maybe =SMALL(A1:A24, COUNTIF(A1:A24, MIN(A1:A24)) + 1)

  3. #3
    Forum Expert Ron Coderre's Avatar
    Join Date
    03-22-2005
    Location
    Boston, Massachusetts
    MS-Off Ver
    2013, 2016, O365
    Posts
    6,996

    Finding the nTH smallest number (ignoring duplicates)

    With
    A1:A24 containing your list of values
    and
    B1: (the rank of the value to find.....eg 5)

    If there will be NO blanks:
    This ARRAY FORMULA returns the list value for that rank (ignoring duplicates):
    Please Login or Register  to view this content.
    If there may be blanks, try this ARRAY FORMULA:
    Please Login or Register  to view this content.
    Note: Commit array formulas with CTRL+SHIFT+ENTER, instead of just ENTER.

    In the above example, and using your data, the 5th smallest value is 16.5.
    Ron
    Former Microsoft MVP - Excel (2006 - 2015)
    Click here to see the Forum Rules

  4. #4
    Forum Expert
    Join Date
    12-24-2004
    Location
    Sweden
    Posts
    1,256

    2nd lowest number (avoiding duplicates)

    Just 4fun
    Here is one more alternative. It is short but it Only works in this example:
    =SMALL(A1:A24,1+FREQUENCY(A1:A24,A1:A24))
    Frequency is normally an Array but if Ctrl+Shift+Enter is omitted Excel will use the first number.
    And since the list is sorted...it works - but just in this case (meaning it's not a reliable solution)

    //Ola

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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