SOLVED

# finding 3 largest and 3 smallest numbers in a spreadsheet

Occasional Contributor

# finding 3 largest and 3 smallest numbers in a spreadsheet

Hi. Im relatively new to excel formulas. Im trying to pull out the 3 largest and 3 smallest numbers in my spreadsheet. How do I do this/

4 Replies
best response confirmed by staciehamilton2370 (Occasional Contributor)
Solution

# Re: finding 3 largest and 3 smallest numbers in a spreadsheet

You can use the LARGE and SMALL functions for this.

To return the 3 largest values in the range A1:Z100:

=LARGE(A1:Z100,{1,2,3})

And the three smallest:

=SMALL(A1:Z100,{1,2,3})

Warning: if there are ties, the results might be unexpected.

# Re: finding 3 largest and 3 smallest numbers in a spreadsheet

Thank you so much!!

# Re: finding 3 largest and 3 smallest numbers in a spreadsheet

For this purpose there are the SMALL() and LARGE() functions. Together with the SEQUENCE() function, you can output all three values simultaneously or calculate them further.

``````=SMALL(A1:A20,SEQUENCE(3))
=LARGE(A1:A20,SEQUENCE(3))``````

I have assumed that the data is available in A1:A20.

# Re: finding 3 largest and 3 smallest numbers in a spreadsheet

thank you!! And yes, you assumed correctly. It's a huge file