Statistical
Nth largest value without duplicates: dense Gold, Silver, Bronze tiers
Wrap UNIQUE inside LARGE(UNIQUE(Range), N) so tied top scores do not swallow 2nd and 3rd place prize tiers.
Why plain LARGE([500, 500, 420], 2) returns 500 instead of 420!
For scores 500, 500, 420, 420, and 310, LARGE(range,2) returns 500 because it ranks observations. LARGE(UNIQUE(range),2) returns 420 because it ranks distinct values.
This is dense ranking: tied scores share a tier and the next distinct score takes the next tier. Use it only when that matches the competition or reporting rules; other ranking schemes intentionally leave gaps.
Use numeric, nonblank scores and a positive integer tier in D2. IFERROR supplies a fallback but can also hide source errors, so validate the inputs separately.
| Tier k | Raw LARGE(B2:B6, k) on [500, 500, 420, 420, 310] | Dense LARGE(UNIQUE(B2:B6), k) |
|---|---|---|
| 1st (Gold) | 500 | 500 |
| 2nd (Silver) | 500 (Duplicate tie!) | 420 (True 2nd distinct score!) |
| 3rd (Bronze) | 420 | 310 (True 3rd distinct score!) |
Nth-largest distinct value
A range containing one distinct value has a valid first tier. Requesting a higher tier produces the fallback.
=IFERROR(LARGE(UNIQUE(B2:B6), D2), "Fewer Distinct Tiers")List all winners tied at the Nth distinct score tier
Finds every rep whose score equals LARGE(UNIQUE(B2:B6), D2).
=IFERROR(TEXTJOIN(", ", TRUE, FILTER(A2:A6, B2:B6 = LARGE(UNIQUE(B2:B6), D2), "None")), "Fewer Distinct Tiers")Try the example
Use the original values or change them to test your understanding.