Microsoft Excel SMALL Function
Part 1: Introduction
Definition
The SMALL function in Microsoft Excel is a statistical function that returns the k-th smallest value in a data set.
Purpose
The SMALL function is used when you want to return values with a particular relative standing in a data set. It helps to identify and extract specific values based on their position when sorted in ascending order.
Syntax & Arguments
The syntax for the SMALL function is as follows:
SMALL(array, k) Arguments:
- Array: This is a required argument. It refers to the array or range of numerical data for which you want to determine the k-th smallest value.
- K: This is also a required argument. It represents the position (from the smallest) in the array or range of data to return.
Return Value
The SMALL function returns the k-th smallest value from the data set or array.
Remarks
- If the array is empty, SMALL returns the #NUM! Error value.
- If k ≤ 0 or if k exceeds the number of data points, SMALL returns the #NUM! Error value.
- If n is the number of data points in the array, SMALL(array,1) equals the smallest value, and SMALL(array,n) equals the most significant value.
Part 2: Examples
Let’s look at some examples of how to use the SMALL function in a business context.
Example 1
Purpose of Example: Find the 2nd most diminutive sales figure from a weekly list.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Week | Sales |
| 2 | Week 1 | 500 |
| 3 | Week 2 | 600 |
| 4 | Week 3 | 450 |
| 5 | Formula | Result |
| 6 | =SMALL(B2:B4, 2) | 500 |
Explanation: In this example, we have a list of sales figures for three weeks. We use the SMALL function to find the 2nd most diminutive sales figure. The formula =SMALL(B2:B4, 2) returns 500, the 2nd most diminutive sales figure in the list.
Example 2
Purpose of Example: To find the 3rd most minor expense from a list of monthly expenses.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Month | Expense |
| 2 | January | 1500 |
| 3 | February | 1200 |
| 4 | March | 1800 |
| 5 | Formula | Result |
| 6 | =SMALL(B2:B4, 3) | 1800 |
Explanation: In this example, we list expenses for three months. We use the SMALL function to find the 3rd smallest expense. The formula =SMALL(B2:B4, 3) returns 1800, which is the 3rd smallest expense on the list.
Example 3
Purpose of Example: To find the smallest number of units sold from a list of product sales.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Product | Units |
| 2 | Product 1 | 50 |
| 3 | Product 2 | 75 |
| 4 | Product 3 | 60 |
| 5 | Formula | Result |
| 6 | =SMALL(B2:B4, 1) | 50 |
Explanation: In this example, we have a list of units sold for three products. We use the SMALL function to find the smallest number of units sold. The formula =SMALL(B2:B4, 1) returns 50, which is the smallest number of units sold.
Example 4
Purpose of Example: To find the 2nd smallest number of employees from a list of departments.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Department | Employees |
| 2 | HR | 15 |
| 3 | Marketing | 20 |
| 4 | Sales | 18 |
| 5 | Formula | Result |
| 6 | =SMALL(B2:B4, 2) | 18 |
Explanation: In this example, we have a list of the number of employees in three departments. We use the SMALL function to find the 2nd smallest number of employees. The formula =SMALL(B2:B4, 2) returns 18, which is the 2nd smallest number of employees.
Example 5
Purpose of Example: To find the smallest revenue from a list of quarterly revenues.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Quarter | Revenue |
| 2 | Q1 | 5000 |
| 3 | Q2 | 5500 |
| 4 | Q3 | 5200 |
| 5 | Formula | Result |
| 6 | =SMALL(B2:B4, 1) | 5000 |
Explanation: In this example, we list revenues for three quarters. We use the SMALL function to find the smallest revenue. The formula =SMALL(B2:B4, 1) returns 5000, which is the smallest revenue.
Example 6: SMALL with IF
Purpose of Example: Find the most diminutive sales figure over $500 from a weekly list.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Week | Sales |
| 2 | Week 1 | 500 |
| 3 | Week 2 | 600 |
| 4 | Week 3 | 450 |
| 5 | Formula | Result |
| 6 | =SMALL(IF(B2:B4>500, B2:B4), 1) | 600 |
Explanation: In this example, we want to find the most diminutive sales figure more significant than $500. We use the SMALL function nested with an IF function. The IF function creates an array of sales figures greater than $500, and the SMALL function returns the smallest value from this array. The formula =SMALL(IF(B2:B4>500, B2:B4), 1) returns 600, which is the most diminutive sales figure over $500.
Example 7: SMALL with SUM
Purpose of Example: Find the minor total sales from a weekly sales list and returns.
Data Tables and Formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Week | Sales | Returns |
| 2 | Week 1 | 500 | 50 |
| 3 | Week 2 | 600 | 75 |
| 4 | Week 3 | 450 | 30 |
| 5 | Formula | Result | |
| 6 | =SMALL(SUM(B2:C2), SUM(B3:C3), SUM(B4:C4)), 1) | 480 |
Explanation: In this example, we want to find each week’s most minor total sales (sales minus returns). We use the SMALL function nested with the SUM function. The SUM function calculates the total sales for each week, and the SMALL function returns the smallest total. The formula =SMALL(SUM(B2:C2), SUM(B3:C3), SUM(B4:C4)), 1) returns 480, which is the smallest total sales.
Example 8: SMALL with VLOOKUP
Purpose of Example: Find the most diminutive sales figure and return the corresponding week.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Week | Sales |
| 2 | Week 1 | 500 |
| 3 | Week 2 | 600 |
| 4 | Week 3 | 450 |
| 5 | Formula | Result |
| 6 | =VLOOKUP(SMALL(B2:B4, 1), A2:B4, 1, FALSE) | Week 3 |
Explanation: In this example, we want to find the week with the most diminutive sales figure. We use the SMALL function nested with the VLOOKUP function. The SMALL function finds the most diminutive sales figure, and the VLOOKUP function returns the corresponding week. The formula =VLOOKUP(SMALL(B2:B4, 1), A2:B4, 1, FALSE) returns “Week 3”, the week with the most diminutive sales figure.
Example 9: SMALL with COUNTIF
Purpose of Example: To find the most diminutive sales figure that has occurred more than once in a weekly sales list.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Week | Sales |
| 2 | Week 1 | 500 |
| 3 | Week 2 | 500 |
| 4 | Week 3 | 450 |
| 5 | Formula | Result |
| 6 | =SMALL(IF(COUNTIF(B2:B4, B2:B4)>1, B2:B4), 1) | 500 |
Explanation: In this example, we want to find the most diminutive sales figure that has occurred more than once. We use the SMALL function nested with the COUNTIF function. The COUNTIF function creates an array of sales figures that have occurred more than once, and the SMALL function returns the smallest value from this array. The formula =SMALL(IF(COUNTIF(B2:B4, B2:B4)>1, B2:B4), 1) returns 500, the smallest sales figure that has happened more than once.
Example 10: SMALL with AVERAGE
Purpose of Example: Find the minor average sales from a weekly sales list and returns.
Data Tables and Formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Week | Sales | Returns |
| 2 | Week 1 | 500 | 50 |
| 3 | Week 2 | 600 | 75 |
| 4 | Week 3 | 450 | 30 |
| 5 | Formula | Result | |
| 6 | =SMALL(AVERAGE(B2:C2), AVERAGE(B3:C3), AVERAGE(B4:C4)), 1) | 275 |
Explanation: In this example, we want to find each week’s most minor average sales (average sales and returns). We use the SMALL function nested with the AVERAGE function. The AVERAGE function calculates the average weekly sales, and the SMALL function returns the minor average. The formula =SMALL(AVERAGE(B2:C2), AVERAGE(B3:C3), AVERAGE(B4:C4)), 1) returns 275, which is the most minor average sales.
Example 11: SMALL with MAX
Purpose of Example: Find the minor maximum sales from a weekly sales list and returns.
Data Tables and Formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Week | Sales | Returns |
| 2 | Week 1 | 500 | 50 |
| 3 | Week 2 | 600 | 75 |
| 4 | Week 3 | 450 | 30 |
| 5 | Formula | Result | |
| 6 | =SMALL(MAX(B2:C2), MAX(B3:C3), MAX(B4:C4)), 1) | 500 |
Explanation: In this example, we want to find the minor maximum sales (maximum of sales and returns) for each week. We use the SMALL function nested with the MAX function. The MAX function calculates the maximum weekly sales, and the SMALL function returns the smallest maximum. The formula =SMALL(MAX(B2:C2), MAX(B3:C3), MAX(B4:C4)), 1) returns 500, which is the smallest maximum sales.
Example 12: SMALL with MIN
Purpose of Example: Find the minor minimum sales from a weekly sales list and returns.
Data Tables and Formulas:
| A | B | C | |
|---|---|---|---|
| 1 | Week | Sales | Returns |
| 2 | Week 1 | 500 | 50 |
| 3 | Week 2 | 600 | 75 |
| 4 | Week 3 | 450 | 30 |
| 5 | Formula | Result | |
| 6 | =SMALL(MIN(B2:C2), MIN(B3:C3), MIN(B4:C4)), 1) | 30 |
Explanation: In this example, we want to find the minor minimum sales (minimum of sales and returns) for each week. We use the SMALL function nested with the MIN function. The MIN function calculates the weekly minimum sales, and the SMALL function returns the smallest minimum. The formula =SMALL(MIN(B2:C2), MIN(B3:C3), MIN(B4:C4)), 1) returns 30, which is the minor minimum sales.
Example 13: SMALL with ROUND
Purpose of Example: Find the most diminutive rounded sales figure from a weekly list.
Data Tables and Formulas:
| A | B | |
|---|---|---|
| 1 | Week | Sales |
| 2 | Week 1 | 500.25 |
| 3 | Week 2 | 600.75 |
| 4 | Week 3 | 450.50 |
| 5 | Formula | Result |
| 6 | =SMALL(ROUND(B2:B4, 0), 1) | 450 |
Explanation: In this example, we want to find the most diminutive sales figure after rounding to the nearest whole number. We use the SMALL function nested with the ROUND function. The ROUND function rounds the sales figures to the nearest whole number, and the SMALL function returns the smallest rounded value. The formula =SMALL(ROUND(B2:B4, 0), 1) returns 450, which is the most diminutive rounded sales figure.
Part 3: Tips and Tricks
Error Handling: Be aware of the #NUM! Error. This occurs if the array is empty or the k value is less than or equal to 0 or greater than the number of data points.
Most considerable Value: Remember that SMALL(array,n) will return the most significant value in the array, where n is the number of data points.
Dynamic K Value: You can dynamically use other Excel functions to determine the k value. For example, you could use the COUNT function always to return the most minor or most significant value.
Non-Numeric Values: The SMALL function will ignore non-numeric values in the array. Consider using a different function or method if you need to include these.
Array Formulas: The SMALL function can be used in an array formula to return an array of the smallest values. This can be useful for more complex data analysis.