Use the SMALL perform to seek out the smallest quantity and the LARGE perform to seek out the most important quantity. Use both Excel perform within the kind =SMALL(vary,place) or =LARGE(vary,place). For instance, =SMALL(B2:E13,1) will discover the primary smallest quantity within the vary of cells between B2 and E13.
When you might have a spreadsheet full of knowledge, discovering the quantity you want will be tedious. But, whether or not you’re in search of the bottom product gross sales quantity or the very best take a look at rating, Excel’s SMALL and LARGE capabilities will help.
Utilizing these two Excel capabilities, you aren’t restricted to solely discovering the smallest or largest quantity in a cell vary. You can even find the second smallest, third smallest, or fifth largest. For a fast approach to discover the quantity you need, right here’s find out how to use the SMALL and LARGE capabilities in Excel.
Use the SMALL Operate
The syntax for the perform is SMALL(vary, place) the place each arguments are required. You’ll enter the vary or array for the vary argument. For the place argument, enter a one for the primary smallest quantity, two for the second smallest quantity, three for the third, and so forth.
For example, we’ll find the smallest quantity in our cell vary B2 by way of E13 utilizing this formulation:
=SMALL(B2:E13,1)
As one other instance, we’ll discover the second smallest quantity in that very same cell vary. Right here’s the formulation:
=SMALL(B2:E13,2)
Use the LARGE Operate
The LARGE perform works the identical approach because the SMALL perform, solely offering you with the largest quantity. The syntax is LARGE(vary, place) with each arguments required and representing the identical knowledge because the SMALL perform.
To seek out the most important quantity in our cell vary B2 by way of E13, we’ll use this formulation:
=LARGE(B2:E13,1)
To find the third largest quantity in that very same cell vary, we are able to use this formulation:
=LARGE(B2:E13,3)
A Limitation to Word
It’s necessary to notice a particular limitation whenever you use these capabilities. When you have duplicate numbers in your knowledge, your result’s skewed when discovering completely different positions. Right here’s an instance.
RELATED: How one can Spotlight Duplicates in Microsoft Excel
Under, we looked for the most important quantity in our cell vary B2 by way of E13. The result’s 1,800, which is appropriate.
However, if we search for the second largest quantity in that very same cell vary, our consequence can also be 1,800. It is because 1,800 seems twice, making it each the most important and second largest quantity.
Think about this limitation when looking for varied positions in your cell vary.
If you wish to shortly discover the bottom 5 gross sales totals in your product sheet or the very best three month-to-month payments in your price range, hold the SMALL and LARGE capabilities in thoughts.
For extra, learn to use INDEX and MATCH to seek out particular values or find out how to discover vary in Excel.