Finding nth Largest or Smallest Value in an Array

When you need to find second largest value or third smallest value in an array of cells, you can do it easily with LARGE() and SMALL() functions. They are very easy to use and they might be more powerful than you might think when used creatively. Here is how to find nth largest or smallest value in an array.

Using LARGE() and SMALL() functions is very easy. All you need to do is to specify the range you want to be evaluated and specify position as a number. Syntax for both functions is very similar:

=LARGE(array; number)

=SMALL(array; number)

Here are some usage examples of these two functions:

Dataset:

Queries:

You can fecth any nth largest or smallest values like this. You can also create dynamic top lists by using these functions. Wonder how, you will need to wait for the next post 😉

You may also like...

Leave a Reply

Your email address will not be published. Required fields are marked *