The SMALL function, one of Excel's statistical functions, can be used to find data based on relative size.
The syntax for the SMALL function is:
= SMALL ( Array , K )
Array - the array or range of cells containing the data to be used in the function.
K - the Kth smallest value, such as the third smallest value, that is being sought.
Example: Using Excel's SMALL Function
For help with this example, see the image to the right.
- Enter the following data into cells D1 to D6: 123,135,147,130,98,187.
- Click on cell E1 - the location where the results will be displayed.
- Click on the Formulas tab.
- Choose More Functions > Statistical from the ribbon to open the function drop down list.
- Click on SMALL in the list to bring up the function's dialog box.
- Click on the Array line in the dialog box.
- Drag select cells D1 to D6 in the spreadsheet to enter the range into the dialog box.
- Click on the K line in the dialog box.
- Type a 3 ( three ) on this line to find the third smallest value in the range selected.
- Click OK.
- The number 130 should appear in cell E1 since it is the third smallest number ( the numbers 123 and 98 in the list are both smaller ).
- The complete function = SMALL ( D1 : D6 , 3 ) appears in the formula bar above the worksheet when you click on cell E1.