finding 3 largest and 3 smallest numbers in a spreadsheet

[staciehamilton2370]

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/

[#2 · Accepted solution]

@staciehamilton2370 

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.

[#3]

Thank you so much!!

[#4]

Hi @staciehamilton2370,

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.

[#5]

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