Finding the Smallest or Largest Number in Excel

The SMALL and LARGE functions in Excel are great tools for finding the smallest or largest numbers in a range of cells. These functions allow you to go beyond just finding the smallest or largest number by specifying the position in the sorted list (e.g., second smallest, third largest, etc.).

Using the SMALL Function

The SMALL function returns the smallest number in a specified range or array. You can adjust the position argument to find the nth smallest number, where 1 refers to the smallest, 2 for the second smallest, and so on.

Syntax:

scssCopy code=SMALL(range, position)

• range: The range of cells or array where you want to find the smallest number.
• position: The position of the smallest number you want. For example, 1 finds the smallest, 2 finds the second smallest, and so on.

Example 1: Find the smallest number

To find the smallest number in the range from B2 to E13, use the formula:

scssCopy code=SMALL(B2:E13, 1)

Example 2: Find the second smallest number

To find the second smallest number in the same range, use:

scssCopy code=SMALL(B2:E13, 2)

Using the LARGE Function

The LARGE function returns the largest number in a specified range or array. Similar to the SMALL function, you can adjust the position argument to find the nth largest number, where 1 refers to the largest, 2 for the second largest, and so on.

Syntax:

scssCopy code=LARGE(range, position)

• range: The range of cells or array where you want to find the largest number.
• position: The position of the largest number you want. For example, 1 finds the largest, 2 finds the second largest, and so on.

Example 1: Find the largest number

To find the largest number in the range from B2 to E13, use the formula:

scssCopy code=LARGE(B2:E13, 1)

Example 2: Find the third largest number

To find the third largest number in the same range, use:

scssCopy code=LARGE(B2:E13, 3)

Limitation to Note

Both functions can return the same value if there are duplicate numbers. For example, if the largest number in the range appears twice, both the LARGE(…, 1) and LARGE(…, 2) will return the same number. This happens because duplicates are treated as separate occurrences at different positions in the sorted list.

For instance:

• If the largest number is 1,800 and it appears twice, the formula =LARGE(B2:E13, 1) will return 1,800.
• The formula =LARGE(B2:E13, 2) will also return 1,800, since there’s another occurrence of 1,800 in the data.

Unlock powerful features with a genuine Office 2021 Professional Plus Key – get the best deal at the lowest price today!