Descriptive statistics
LARGE, SMALL, and RANK.EQ: extract Nth values and handle ties
Pull the 1st, 2nd, or 3rd highest and lowest numbers without sorting the table, see how tied scores repeat in LARGE and skip ranks in RANK.EQ, and catch #NUM! bounds.
Four audit scores including a tie for first place
Branches A through D scored 92, 85, 92, and 74 (B2:B5). Two branches share the top score of 92.
| Branch (A) | Score (B) | RANK.EQ(B, $B$2:$B$5) |
|---|---|---|
| Branch A | 92 | 1 |
| Branch B | 85 | 3 |
| Branch C | 92 | 1 |
| Branch D | 74 | 4 |
How LARGE and SMALL treat duplicate values
LARGE(array, k) returns the k-th largest value, counting every occurrence rather than deduplicating: LARGE(B2:B5, 1) is 92, LARGE(B2:B5, 2) is also 92, and LARGE(B2:B5, 3) reaches 85. SMALL(B2:B5, 1) returns the lowest score, 74.
=LARGE(B2:B5,E2)Competition ranking in RANK.EQ and out-of-range k
RANK.EQ(B3, $B$2:$B$5) assigns rank 1 to both 92s and skips rank 2, giving 85 a rank of 3. Requesting k <= 0 or k greater than the numeric count—such as LARGE(B2:B5, 5)—returns #NUM!.
=RANK.EQ(B3,$B$2:$B$5)Try the example
Use the original values or change them to test your understanding.