How to find the 2nd (or Nth) largest or smallest value
✓ Verified in LibreOffice 25.8.7.3Return the Nth biggest or smallest number in a range, e.g. the runner-up, not just the max.
The formula
| App | Formula | Notes |
|---|---|---|
| Excel | =LARGE(A2:A9,2) | 2nd largest. Use SMALL(range,2) for 2nd smallest; change 2 to N. |
| Google Sheets | =LARGE(A2:A9,2) | Identical. |
| LibreOffice Calc | =LARGE(A2:A9,2) | Identical. |
How it works
LARGE(range, k) returns the k-th largest value; SMALL(range, k) the k-th smallest. MAX is just LARGE(range,1). Among 10/50/20/40/30 the 2nd largest is 40.
Verified, not just documented
We ran =LARGE(A2:A6,2) in LibreOffice 25.8.7.3 (headless, with forced recalculation) and it returned 40 — exactly the expected result. Every formula here is confirmed by actually executing it.