This page is an advertiser-supported excerpt of the book, Learn Excel 2007-2010 from MrExcel - 512 Excel Mysteries Solved. If you like this topic, please consider buying the entire e-book.
Find the Second Largest Value
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.
Figure 382 Use LARGE and SMALL to return the kth largest value.