From Ted French,
Your Guide to Spreadsheets.
FREE Newsletter. Sign Up Now!

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 K^th smallest value, such as the third smallest value, that is being sought.

Example Using Excel's SMALL Function:

Note: For help with this example, see the image to the right.

1. Enter the following data into cells D1 to D6: 123,135,147,130,98,187.
   
   
2. Click on cell E1 - the location where the results will be displayed.
   
   
3. Click on the Formulas tab.
   
   
4. Choose More Functions > Statistical from the ribbon to open the function drop down list.
   
   
5. Click on SMALL in the list to bring up the function's dialog box.
   
   
6. Click on the Array line in the dialog box.
   
   
7. Drag select cells D1 to D6 in the spreadsheet to enter the range into the dialog box.
   
   
8. Click on the K line in the dialog box.
   
   
9. Type a 3 ( three ) on this line to find the third smallest value in the range selected.
   
   
10. Click OK.
    
    
11. 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 ).
    
    
12. The complete function = SMALL ( D1 : D6 , 3 ) appears in the formula bar above the worksheet when you click on cell E1.

Related Articles

• Excel 2007 RANK Function
• Excel 2007 LARGE Function