Find the Second Largest Value

March 16, 2022 - by Bill Jelen

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.

Ten numbers appear in A2:A11. The numbers 1-10 appear in C2:C11. The figure shows sorting high to low using =LARGE($A$2:$A$11,C2) and copying down. Also sorting low to high using =SMALL with the same arguments. — Figure 377. Use LARGE and SMALL to return the kth largest value.

This article is an excerpt from Power Excel With MrExcel

Title photo by Jess Bailey on Unsplash