[ad_1]
Use the SMALL function to find the smallest number and the LARGE function to find the largest number. Use the Excel function in the form =SMALL(range,position) or =LARGE(range,position). For example, =SMALL(B2:E13,1) will find the first smallest number in the cell range between B2 and E13.
When you have a spreadsheet full of data, finding the number you need can be tedious. However, whether you’re looking for the lowest number of product sales or the highest test score, Excel’s SMALL and LARGE functions can help.
With these two Excel functions, you are not limited to finding only the smallest or largest number in a range of cells. You can also locate the second smallest, third smallest, or fifth largest. For a quick way to find the number you want, here’s how to use the SMALL and LARGE functions in Excel.
Use the SMALL function
The function syntax is SMALL(range, position) where both arguments are required. You will enter the range or matrix for the range plot. For him position argument, enter one for the first smallest number, two for the second smallest number, three for the third, and so on.
As an example, we’ll locate the smallest number in our cell range B2 through E13 using this formula:
=SMALL(B2:E13,1)
As another example, we’ll find the second smallest number in that same range of cells. Here is the formula:
=SMALL(B2:E13,2)
Use the LARGE function
The LARGE function works the same way as the SMALL function, it just gives you the largest number. the syntax is LARGE(range, position) with both arguments required and representing the same data as the SMALL function.
To find the largest number in our range of cells B2 to E13, we’ll use this formula:
=LARGE(B2:E13,1)
To locate the third largest number in that same range of cells, we can use this formula:
=LARGE(B2:E13,3)
A limitation to notes
It is important to note a specific limitation when using these features. If you have duplicate numbers in your data, your result is skewed by finding different positions. Here is an example.
RELATED: How to highlight duplicates in Microsoft Excel
Next, we look for the largest number in our range of cells B2 to E13. The result is 1,800, which is correct.
But, if we find the second largest number in that same range of cells, our result is also 1,800. This is because 1800 appears twice, making it the largest and second largest number.
Consider this limitation when searching for multiple positions in your cell range.
If you want to quickly find the five lowest sales totals on your item sheet or the three highest monthly invoices on your budget, consider the SMALL and LARGE functions.
For more information, learn how to use INDEX and MATCH to find specific values or how to find a range in Excel.
[ad_2]
