ArtsAutosBooksBusinessEducationEntertainmentFamilyFashionFoodGamesGenderHealthHolidaysHomeHubPagesPersonal FinancePetsPoliticsReligionSportsTechnologyTravel

Find the 2nd Smallest Number in a List

Updated on October 16, 2010

When summarising data in Excel it is common for you to need to find the smallest numbers in a list. You have probably used the MIN function before to find the smallest number. But how do you find the second, third or fourth smallest?

The SMALL function can be used to find the smallest numbers in a list. Let’s use the SMALL function to find the 2nd and 3rd smallest numbers in the list below.

Finding the 2nd and 3rd smallest numbers
Finding the 2nd and 3rd smallest numbers

For the SMALL function to work, you need to supply the cell range and the rank of the number you want returned.

To return the 2nd smallest number in cell E2, enter:

=SMALL(B2:B8,2)

The B2:B8 represents the list you want to search within.

The 2 represents the rank of the number returned i.e. second smallest.

Use the SMALL function to find he second smallest number
Use the SMALL function to find he second smallest number

To find the third largest number in the list you would then enter the function below in cell E3.

=LARGE(B2:B8,3)

The number 2 was changed to a 3 to return the third smallest number.

Functions like VLOOKUP, INDEX and MATCH can then be used to return related information about the 2nd or 3rd smallest values such as the companies name.

Find more Microsoft Excel functions.

Comments

    0 of 8192 characters used
    Post Comment

    No comments yet.

    Click to Rate This Article