LARGE and SMALL Functions
The LARGE and SMALL functions are used to find the highest and lowest values from a list of numbers based on their position.
- LARGE returns the Nth largest value.
- SMALL returns the Nth smallest value.
LARGE Function
The LARGE function returns the K-th largest value from a range.
Syntax
LARGE(array, k)
| Argument | Description |
|---|---|
| array | Range containing numbers. |
| k | Position of the largest value to return. |
Sample Data
| Marks |
|---|
| 85 |
| 92 |
| 78 |
| 95 |
| 88 |
Examples
| Formula | Result | Explanation |
|---|---|---|
| =LARGE(A2:A6,1) | 95 | 1st Largest Value |
| =LARGE(A2:A6,2) | 92 | 2nd Largest Value |
| =LARGE(A2:A6,3) | 88 | 3rd Largest Value |
Tip: Use LARGE to find Top 5 Students, Top 10 Sales Values, Highest Salaries, etc.
SMALL Function
The SMALL function returns the K-th smallest value from a range.
Syntax
SMALL(array, k)
| Argument | Description |
|---|---|
| array | Range containing numbers. |
| k | Position of the smallest value to return. |
Examples
| Formula | Result | Explanation |
|---|---|---|
| =SMALL(A2:A6,1) | 78 | 1st Smallest Value |
| =SMALL(A2:A6,2) | 85 | 2nd Smallest Value |
| =SMALL(A2:A6,3) | 88 | 3rd Smallest Value |
Tip: Use SMALL to find the Lowest Marks, Lowest Sales, Cheapest Products, etc.
Common Errors
| Error | Reason |
|---|---|
| #NUM! | k is greater than the number of values in the range. |
| #VALUE! | Invalid argument supplied. |
