The Excel Small function is a statistical function that is used to return the numeric value in a list based on the position specified by the user. The function evaluates numeric value in a data set in ascending order, and then selects the nth smallest value chosen by the user. For example, the 1st smallest value, 2nd smallest value, 3rd smallest value, 4th smallest value and so on. Our mission today is to take you through the SMALL Function and how you can employ it for use in financial modeling. Before we delve into that, let’s take a look at the Syntax and argument of the SMALL function in excel.
Syntax & Argument
The syntax of the SMALL Function is as follows:
=SMALL(array, k)
There are just 2 arguments in this function, let’s see what they mean below:
- Array: The array or range of numerical you want to check for the nth smallest value
- K: This is the position (represented by an Integer) that specifies the value you want to return from the array or range. 1 corresponds to the smallest value in the array, 2 corresponds to the second smallest value in the array and so on.
To get the nth largest value (antithesis of this function), please see the LARGE Function. Now it is time to take some examples on the SMALL function.
SMALL Function Examples
Basic Example
In this section we would return the second smallest number from a set of hardcoded numbers
Now, using the same example we used in our LARGE Function, let’s illustrate with the scores of the students in a quiz. Our goal would be to calculate the lowest score, second lowest and third lowest scores.
To return the last, 2nd to last and 3rd to last positions, we use the formula below:
=SMALL(D3:D22,2)
=SMALL(D3:D22,3)
Notice that we have the same number for last and 2nd to last. This is because Anne Davis and Sarah Meyer had the same score and were joint lowest. Done with this section, let’s see how we can use the SMALL Function in Financial Modeling
Integrating the Small Function in Financial Modeling
Similar to the LARGE function, we have carved out the capacity price per period calculation for a Power generation company. This time however, we are arranging the scenarios in ascending order from Rows 7 to Row 9.
In Cell 12, we have created a capacity price scenario selector. The number 1 represents the smallest price (Low case), number 2 the second smallest (base case) and number three the third smallest (High case). Because we have arranged our data in ascending order, we can be assured that the price scenario would always correlate with the SMALL function since the function also sorts in ascending order and selects the position K specified by the user.
Now we can go to cell D15 and type in the formula below
Breaking it down, we first select our range D7:D9 as the array argument, then we select the price scenario in cell D12 as our K argument. Finally, we multiply by the number of days in a month since we are calculating the capacity price for each month. Remember to press enter and you are done.
Now copy the formula in cell D15 by using Ctrl + C, and paste it across to adjacent cells E15 : J15 by using Ctrl + V or pressing the enter key.
Khalas, all done. If you dont want to go through the stress of having to sort your data in order to use the SMALL function to select your preferred scenario, then you should try using the CHOOSE Function instead. Kindly check our post here for a detailed guide on using the CHOOSE Function.
