SMALL Excel Formula

Author Zaheer    Category Formulas     Tags ,

Microsoft Excel is very useful in the field of mathematics. This software program has the ability to perform simple and even complex mathematical calculations. Excel can perform the basic mathematical operations such as multiplication, division, addition and subtraction as well as other complicated operations in mathematics. This software program has a function known as the SMALL function which is useful to find the smallest value (depending on the rank) in a given set of values. The best way to start learning about this function is to know its basic description. So what is SMALL function in Excel?

Overview of SMALL Function in Excel

This Excel function is utilized to return the x’th smallest value from a range or an array of cells which contains the numeric values. For instance, you could use the SMALL function to return the 3rd or 4th smallest numeric value from a range. The function’s formula is given below:

SMALL (array, k)

In this formula, the arguments are the following:

  • Array – This is a required argument and it refers to the range of values from which you desire to return the x’th smallest number of.
  • K – This is also a required argument and it is the position of the smallest value within the range of numeric values (i.e. first, second, thirrd, fourth and so on).

You can supply the array argument in the formula of the function as either of the following:

  • A range or cell references which contain the numeric values.
  • The actual numeric values.
  • If you supplied text values in the cell range, the function will ignore these values.

Examples on How to Use the SMALL Function Microsoft Excel

This article will provide you an example on how to use the SMALL function so it will be easier for you to easily learn and understand its usage.

  • Let’s say that you have the following values in cells B1 to B5: 6, 12, 15, 1, 4. Then you desire to find for the 5th, 4th, 3rd, 2nd and 1st smallest numeric value  from the given set of numbers.
  • To find for the 1st smallest value, type in this formula in any cell where you want the result to appear, =SMALL(B1:B5, 1). The function will return the result which is obviously 1.
  • To return the second smallest value, enter this formula in any cell, =SMALL(B1:B5, 2). The function will return the result which is 4.
  • For the 3rd smallest value, you can use this formula, =SMALL(B1:B5, 3). The SMALL function will instantly return the result which is 6.
  • To find for the 4th smallest value, type in this formula in any cell, =SMALL(B1:B5, 4). The function will return the result which is 12.
  • Finally, for the 5th smallest value, enter this formula in any cell, =SMALL(B1:B5, 5). The function give the result which is 15.

You have to take note the any calls to SMALL function wherein the “k” argument is equal to 1, returns a result similar to that of the MIN function.

Common Error in Using the SMALL Function

If you obtained an error in using the SMALL function, this would possibly to be #NUM! value. The #NUM! error value is displayed if the supplied is smaller than 1 or larger than the values within the given range of numeric values.

Post comment