Problem: I can find the largest and smallest numbers using MAX and MIN. I am trying to identify the largest and smallest three numbers. How can I find the second largest number?
Strategy: Use the LARGE or SMALL functions. These functions take a range of values, then a k value. If you use a k value of 1, the LARGE function is exactly like a MAX: =LARGE(B2:B100,1). The real value in LARGE is the ability to ask for the second largest value using =LARGE(B2:B100,2).
In the figure below, you can see the LARGE and SMALL for an entire set of 10 data points. Note that 66 is reported as both the 5th and 6th largest value due to two 66 entries in the original data set.
- Use LARGE and SMALL to return the kth largest value.