How to use SMALL function in Microsoft Excel

 

In this article, we will learn about SMALL function in Microsoft Excel.

In Excel, SMALL function is used to return the k’th smallest value from an array or a range of numerical values.

image 1

 

Let’s take examples and understand:

We have taken 7 different examples for the usage of SMALL function in Microsoft Excel on the basis of following sample data.

image 2

 

1st Example:-

In this example, we will learn how to find out the smallest number within a range.

image 3

 

Follow the steps given below:-

  • Enter the function in cell A14
  • =SMALL(A8:A12,1)
  • Press Enter

image 4

 

The function has given 1. SMALL function takes two arguments, where first argument is an array and the second is the k’th value.
 
2nd Example:-

In this example, we will learn how we can find out the 2nd smallest time value.

image 5

 

Follow the steps given below:-

  • Enter the function in cell B14
  • =SMALL(B8:B12,2), Press Enter

image 6

 

The function has returned 0:05:00, which is 2nd smallest value in the defined range.

Note: To get the smallest number according to different position, we have to change the kth value’s number.
 
3rd Example:-

In this example, we have included text in the range for which we want to return the smallest value.

image 7

 

Follow the steps given below:-

  • Enter the function in cell C14
  • =SMALL(C8:C12,1)
  • Press Enter

image 8

 

The function has returned 4, which is the 1st smallest value within a range. It means function will ignore the cell which contains text and consider cells that contain numbers value only and returns 4, which is the smallest value in the range.

 

4th Example:-

In this example, we will see what happens if range contains blank cells from which we want to return 2nd smallest value.

image 9

 

Follow the steps given below:-

  • Enter the function in cell D14
  • =SMALL(D8:D12,2), Press Enter

image 10

 

Function returns the 2nd smallest value from the entered range. And, as we hit enter we get 3, which is the 2nd smallest value in the range. It means SMALL function ignores blank cells as well.
 
5th Example:-

In this example, we will see what happens if we enter k’th value larger than the number of data points in array.

image 11

 

Follow the steps given below:-

  • Enter the function in cell E14
  • =SMALL(E8:E12,7), Press Enter

image 12

 

Function has returned #NUM error. It is because K’th value should not lie above the data point’s number or else it will return #NUM error.

 

6th Example:-

In this example, we will see what happens when we enter the SMALL function for blank range.

image 13

 

Follow the steps given below:

  • Enter the function in cell F14
  • =SMALL(F8:F12,1), Press Enter

image 14

 

It gives us #NUM error again because both arguments are a must and cannot be empty, which is why function returned an error here.

7th Example:-

In this example, we will see what result we get when our defined range contains any error value.

image 15

 

Follow the steps given below:-

  • Enter the function in cell G14
  • =SMALL(G8:G12,1), Press Enter

image 16

 

The function has returned the #N/A error because there is #N/A error in the range.

So, this is how SMALL function works, and we can make use of it in different situations.

image 48

 

Video: How to use SMALL function in Microsoft Excel

Click on the video link for quick reference to the use of SMALL function. Subscribe to our new channel and keep learning with us!

 

If you liked our blogs, share it with your friends on Facebook. And also you can follow us on Twitter and Facebook.
We would love to hear from you, do let us know how we can improve, complement or innovate our work and make it better for you. Write us at info@exceltip.com

 
 



Example:


Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>