exceltestpractice
On the clock 0:00Free practice test
Practice testsLearnFunctionsShortcutsPricingFree practice test

Function guides ยท Statistical

SMALL function

Returns the k-th smallest value in a range.

Syntax

=SMALL(array, k)
ArgumentWhat it is
arrayThe range of numbers.
kThe rank from the bottom: 1 is the smallest, 2 the second smallest, and so on.

What SMALL does

SMALL returns the value at a given rank from the bottom. SMALL(range,1) is the lowest, SMALL(range,2) the next lowest, and so on.

It is LARGE counted from the other end. Use it for the runner-up price, the bottom three scores, or the third-earliest date in a list. Text and blank cells are ignored and an out-of-range rank returns #NUM!.

On its own SMALL returns a value, so naming the item with the second-lowest price takes an INDEX and MATCH around it.

A related use is finding gaps. The smallest value above a threshold is SMALL with a COUNTIF offset, and the nearest earlier date to a target is MAX with a condition. These come up in data tasks such as finding the next available appointment slot or the cheapest quote that still meets a minimum.

Ranks in SMALL and LARGE are by position in sorted order, not by item. Two items with the same price take consecutive ranks, so a list of prices 5, 5, 8 has SMALL(range,1) and SMALL(range,2) both equal to 5, and SMALL(range,3) equal to 8. Plan for ties whenever you look up a name from the result.

SMALL examples

1. Second lowest price

Second lowest price. The formula =SMALL(B2:B5,2) in cell B7 returns 9.5.
AB
1ProductPrice
2Stapler12.5
3Tape3.25
4Lamp34
5Pens9.5
6
79.5

SMALL with k = 2 skips the cheapest item and returns the next price up, 9.5. The cheapest is 3.25.

2. Total of the bottom two

Total of the bottom two. The formula =SUM(SMALL(B2:B5,{1,2})) in cell B7 returns 12.75.
AB
1ProductPrice
2Stapler12.5
3Tape3.25
4Lamp34
5Pens9.5
6
712.75

An array constant requests several ranks at once. SMALL(range,{1,2}) returns the two lowest values, 3.25 and 9.5, and SUM adds them.

3. Smallest positive number

Smallest positive number. The formula =SMALL(A2:A6,COUNTIF(A2:A6,"<=0")+1) in cell B2 returns 8.
AB
1Balance
2-408
30
415
58
660

MIN would return the negative value. To get the smallest value above zero, use the count of non-positive numbers as an offset. COUNTIF counts the values at or below zero, and SMALL then skips them.

Common SMALL mistakes

  • Asking for a rank above the count. SMALL(range,10) on four values returns #NUM!.
  • Expecting blanks to count as zero. Blank cells are skipped. A zero typed into a cell is a value and will be the smallest.
  • Ties. Equal values take separate ranks. SMALL(range,1) and SMALL(range,2) can return the same number.
  • Using SMALL where a conditional minimum is needed. SMALL does not filter. For the smallest within a group use MINIFS.
  • Hard-coded rank. Put k in a cell so the formula can fill down a list.

SMALL on Excel job tests

Prompts for "the second lowest", "the bottom three" or "the smallest value greater than zero" call for SMALL. It also finds the n-th earliest date, since dates are numbers.

The smallest positive value is MINIFS with ">0" in recent Excel, or SMALL with a COUNTIF offset in older versions. Put the rank k in a cell and refer to it so the formula can be filled down a list. If a test supplies a cell for k, use it instead of typing a number.

  • LARGEReturns the k-th largest value in a range.
  • MINReturns the smallest number in a set.
  • RANK.EQReturns the rank of a number within a list, with ties sharing the same rank.
  • INDEXReturns the value at a given row and column position in a range.

SMALL questions

What is the difference between SMALL and MIN?
MIN returns the smallest value only. SMALL returns the k-th smallest, with SMALL(range,1) equal to MIN.
How do I find the smallest number above zero?
Use MINIFS(range,range,">0") in Excel 2019 or later, or SMALL with a COUNTIF offset in older versions.
Can SMALL return dates?
Yes. The n-th earliest date is SMALL on the date column, formatted as a date.
Why does SMALL return
k is larger than the count of numbers, or the range holds no numbers.

Practice SMALL

The lesson PERCENTILE, LARGE and SMALL (module 20 of Analyst) has checked SMALL exercises. It is part of Full Access; only the first two lessons of each module are free. The free 5-item sample test uses the same kind of formulas in a working grid if you want to try before you decide.

Try a 5-item Excel test sample