The SMALL function in Excel is used to find the k-th smallest value in a data set. This function is particularly useful when you want to extract one of the smallest values from a range of numbers, such as the smallest, the second smallest, or the third smallest value, and so on.

Here’s how you can use the SMALL function:

Syntax:

SMALL(array, k)

• array: This is the range or array of numerical values from which you want to find the k-th smallest value.
• k: This indicates which smallest value you want to obtain. For example, if k = 1, SMALL will return the smallest value in the array; if k = 2, it will return the second smallest, and so on.

Steps to Use SMALL Function:

• Select a Cell: Click on the cell where you want the result of the SMALL function to appear.
• Enter the Formula:
• Begin with an equal sign (`=`) followed by `SMALL`.
• Open a parenthesis and specify the `array` which contains your data.
• Enter a comma, and then type `k`, which specifies the position of the smallest value you want to find.
• Close the parenthesis and press Enter.

Example: To find the 3rd smallest number in a range from A1 to A10, you would use the formula:

   =SMALL(A1:A10, 3)

• Press Enter: After typing the formula, press Enter. The cell will display the k-th smallest value from the specified range.

Examples:

  =SMALL(B2:B15, 1)

• Find the Smallest Value:

This returns the smallest value from the range B2 to B15.

  =SMALL(C1:C20, 2)

• Find the Second Smallest Value:

This returns the second smallest value from the range C1 to C20.

  =SMALL(D5:D50, 3)

• Find the Third Smallest Value:

This returns the third smallest value from the range D5 to D50.

Notes:

• If `k` is less than 1 or greater than the number of data points in the array, SMALL returns a `#NUM!` error.
• The function ignores non-numeric values in the array.
• Useful in combination with functions like INDEX or IF to extract or conditionally format data.

By using the SMALL function, you can efficiently analyze data sets to find values that rank at specific positions in terms of their size.

Follow us on Social Media

facebooklinkedin

Unlock Your Potential

Excel

Access

Power BI

Help us grow the project