This guide will explain how to use the Excel SMALL function, and provide example use cases.
We will cover:
- What is the Excel SMALL function?
- The syntax
What is the Excel SMALL function?
Excel has a large range of useful statistical functions that can be used to easily and quickly analyse a set of raw data. Some are very simple, such as the CEILING and Factorial, while others perform more complex analysis such as a One Way ANOVA.
The SMALL function within Excel is one of the more simple statistical techniques that can be performed, with one very specific purpose.
What the SMALL function allows us to do is look at a set of data, and calculate the kth smallest numerical value within that data set.
By k, we mean a value that is input by the user. If we choose 2, then we mean the 2nd smallest value, if we enter 15, then we mean the 15th smallest value, and so on.
There are of course other ways to find this kind of information within a set of data. If the range we are looking at is relatively small, and we just want the smallest, second smallest or similar value the easiest way is often to just sort the data from smallest to largest and count our way down.
Typically however with data it is best practice not to do this, especially when working with larger sets of data and multiple columns as it can be easy to accidently manipulate the data and cause inaccuracies along the way.
This is where functions such as SMALL come in super handy.
Being a simple calculation, the formula itself is also very easy to write. It is as follows:
Let’s break this down:
array: This simply refers to the overall set of values in Excel that we are looking to measure. If we have a set of values in column A going all the way from the top row to the 10th, we would enter A1:A10.
k: This value refers to the value we want to pull out. 1 gives us the smallest number, 2 gives us the second smallest, 3 the third smallest, and so on.
Using the above example of data in column A, if we wanted the 2nd smallest number the formula would look like this:
Let’s have a quick look at some examples of this in action.
In our data set we have a single column worth of values – leading to 8 figures in total in a random order.
Using the SMALL formula, we can quickly calculate which is the smallest value, in this case 3, which is the largest value by entering the number 8 as there are that many figures, which provides us with 10,000, and a range of other values. See below example:
As you can see from the formula outlined above, and the resulting values there isn’t a whole lot to this one! As mentioned above in this example it might just be easier to re-arrange the data itself given its one column and a very small set of data overall, but when working with larger datasets across multiple columns this formula can save a lot of trouble.
In our example above, we found a way to calculate the largest number in the dataset by entering the number of values total as the k argument in our formula. While this worked fine, there is an alternative here that is far easier – the Excel LARGE function.
As you may have guessed this formula is exactly the same in concept as SMALL, but in reverse! It provides the kth largest value within a dataset.
This sums up our guide on how to use the Excel SMALL function. For more tips and tutorials on using Excels built in functions be sure to check out our Excel Tips page