Top and Bottom With LARGE and SMALL
Pull the nth largest or smallest value from a range.
Beyond MAX and MIN
MAX gives you the single largest value and MIN the single smallest. But what if you want the 2nd highest, or the 3rd lowest?
That is exactly what LARGE and SMALL do. They let you pull the nth biggest or nth smallest value from a range, which is the foundation of top-N and bottom-N reports.
The LARGE Function
LARGE(range, k) returns the kth largest value. The second argument k picks the position: 1 is the largest, 2 the second largest, and so on.
So LARGE(A2:A20,1) equals MAX(A2:A20), while LARGE(A2:A20,2) gives the runner-up.
=LARGE(A2:A20,2)All lessons in this course
- Center With MEDIAN and MODE
- Spread With STDEV and VAR
- Ranking With RANK and PERCENTILE
- Top and Bottom With LARGE and SMALL