Find the Third Smallest or Sixth Largest Number in Excel

Excel's LARGE and SMALL Functions

Find the nth Largest or Smallest Number in Excel with the LARGE and SMALL Functions
Find the nth Largest or Smallest Number in Excel with the LARGE and SMALL Functions. © Ted French

LARGE and SMALL Function Overview

Excel's MAX and MIN functions are handy for finding the largest and smallest numbers in a data set, but not so good when it comes to finding say the third smallest or the sixth largest value in a list of numbers.

The LARGE and SMALL functions, on the other hand, were designed for just this purpose and make it easy to find data based on its size relative to the other numbers in a set of data - whether it is the third, ninth, or ninety ninth largest or smallest number in a list.

Even though they only find numbers, like MAX and MIN, depending on how those numbers are formatted,  the LARGE and SMALL functions can be used to find a wide range of data as shown in the image above where the LARGE function is used to find:

  • third largest negative number - row 2;
  • the second oldest date - row 4 and 5;
  • the third fastest time - row 6;

Similarly, the SMALL function is used to find:

  • the second smallest currency amount - row 7;
  • the third smallest fraction - row 8;

The LARGE and SMALL Functions' Syntax and Arguments

A function's syntax refers to the layout of the function and includes the function's name, brackets, and arguments.

The syntax for the LARGE function is:

= LARGE ( Array , K )

While the syntax for the SMALL function is:

= SMALL ( Array , K )

Array (required) - the array or range of cell references containing the data to be searched by the function.

K (required) - the Kth value being sought - such as the third largest or smallest value in the list. This argument can be the actual number or a cell reference to the location of this data in a worksheet.

Using Cell References for K

An example of using a cell reference for this argument is shown in row 5 in the image, where the LARGE function is used to find third oldest date in the range A4: C4 above it.

An advantage of entering a cell reference for the K argument is that it allows you to easily change the value sought - from second to third to fifty fifth - without modifying the formula itself.

Note: The #NUM! error value is returned by both functions if:

If K is greater than the number of data entries in the Array argument - as shown in row 3 in the example.

  • If K is a negative number - as shown in row 9 in the example;
  • If the range of cell references listed for the Array argument do not contain any number data - as shown in row 10 in the example above.

LARGE and SMALL Function Example

The information below covers the steps used to enter the LARGE function into cell E2 in the image above. As shown, a range of cell references will be included as the number argument for the function.

One advantage of using cell references or a named range is that if the data in the range changes, the results of the function will automatically update without having to edit the formula itself.

The same steps can be used for entering the SMALL function.

Entering the LARGE Function

Options for entering the formula include:

  • typing the formula containing the function =LARGE(A2:C2,3) directly into cell E2 and pressing the Enter key on the keyboard;
  • entering the arguments using the LARGE function's dialog box;

Although it is possible to just type the complete function manually, many people find it easier to use the dialog box as it takes care of entering the function's syntax - such as brackets and comma separators between arguments.

Opening the LARGE Function's Dialog Box

The steps used to open the dialog box for both functions are:

  1. Click on cell E2 - the location where the results will be displayed
  2. Click on the Formulas tab
  3. Choose More Functions > Statistical from the ribbon to open the function drop down list
  4. Click on LARGE  in the list to bring up the desired function's dialog box

Example: Using Excel's LARGE Function

  1. Click on the Array line in the dialog box;
  2. Highlight cells A2 to A3 in the worksheet to enter the range into the dialog box;
  3. Click on the K line in the dialog box;
  4. Type a 3 ( three ) on this line to find the third largest value in the range selected;
  5. Click OK to complete the function and close the dialog box;
  6. The number -6,587,449 should appear in cell E2 since it is the third largest number ( remember negative numbers get smaller the further they are from zero);
  7. If you click on cell E2, the complete function =LARGE(A2:C2,3) appears in the formula bar above the worksheet.