After my Advanced Excel workshop in Singapore, one of the participants, Lai Thim asked me if there is a way to find the second largest value, or the third largest value given in a data set.
Finding the Largest Value
This is extremely easy. Just use the Max() function. It will give you the highest value. Similarly, use the Min() function to get the smallest value.
But to get the second largest value, using a formula, you can’t use Min() or Max() functions.
Using LARGE Function
Microsoft Excel has a hidden function, often unknown singe the late nineties, which can do the job perfectly.
Let’s say you have values from cell A1 to A5.
To get the Second Largest value, you could write the following formula in Excel:
This will give you the second largest value, without any fuss.
The format is =LARGE(array, k), and it picks up the Kth largest value from the array.
A Tiny Cousin Brother – SMALL
Just like Large, SMALL function will pick up the Second Smallest value for that matter.
=SMALL(A1:A5, 3) would give you the third smallest value.
That’s it. Give it a try. This is a quick and dirty way to find such small or large values. These are not new functions… Have been there in Excel since 1995… 🙂
Vinai loves to share new and funky ways to find information from our data, using everyday Excel techniques.
Vinai also conducts in-house and public runs of his famous Advanced Excel Techniques, and Excel Dash-boarding Techniques in Singapore, Malaysia, Indonesia, Dubai, Hong Kong, and many other countries around the globe. Connect with Vinai at [email protected].
Are you facing any problem in using Excel? Any Question?
You have come to the right place. Tell us your needs. We’ll be glad to help you!