How to find nth smallest value in a data table

by | Jun 5, 2018 | Excel Tips & Tricks

How to get bottom nth value

This article shows you how to find nth smallest value in a data table by using the SMALL function.

Syntax

=SMALL(absolute data range reference, nth)

Steps

  1. Start with =SMALL( function
  2. Select the range that contains the values $B$2:$G$7,
  3. Select the cell that contains the nth value or you can manually enter the value I3
  4. Type ) to finish and complete the formula.

How

The SMALL function returns the nth smallest value in a data table. The SMALL function has two arguments; first one is an array of values, and the second one is the position of the smallest value.

=SMALL($B$2:$G$7,I3)

The function returns #NUM! under these circumstances:

  • If array is empty
  • If n is greater that the number of data points. For example; if array has 5 items, however you try to get 10th smallest item.
  • If n <= 0

To get the minimum value, you can also use the MIN function.

Also see related article how to find nth largest value in a data table.