Finding the nth smallest number in a range, discounting duplicates

Posted on 2011-10-19
Last Modified: 2012-05-12
I used to know how to do this I think, but I can't find it anywhere. I need something as simple as possible that when I drag it down, each succeeding row gives me the next smallest unique number in the range [PercentsAll].

In other words if the lowest number is 92.53%, and there are 3 such occurrences, I can't have "=SMALL(PercentsAll,ROW(A2))" return 92.53%. I need it to return the next lowest unique number 93.15%

The method I used to know involved a fairly complicated formula, so I'm hoping there's a simpler one out there.



Open in new window

Question by:gabrielPennyback
    LVL 50

    Expert Comment

    by:barry houdini
    Try using this formula in the first cell, e.g. in C2


    Then in C3 use this array formula confirmed with CTRL+SHIFT+ENTER and copied down


    regards, barry
    LVL 50

    Accepted Solution

    Sorry, the formula for C3 should refer to the cell above, C2, not C3, like this


    See attached

    regards, barry
    LVL 11

    Expert Comment

    If this is a manual process I would

    Take the numbers your interested in an filter the list by unique records.  

    Then I would just sort it ascending or decending.  

    If you need a complex formula to do it, just let me know.
    LVL 11

    Expert Comment

    You would filter the list by unique records by selecting data, then in the filter section advanced, then check the unique records box.
    LVL 50

    Expert Comment

    by:barry houdini
    An improvement to my suggestion...

    Use this formula for a count of the different values


    then the formula to list the unique values from smallest to largest can be amended so that it can be copied down further than you need, extra cells will be blank - revised formula in D3 copied down


    regards, barry
    LVL 1

    Author Closing Comment

    This is perfect, thanks, Barry. And I love how simple it is. :-)

    - John

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Suggested Solutions

    I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows an…
    Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
    The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

    737 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now