For many years, the MAX and the MIN functions have overshadowed Excel’s LARGE and SMALL functions. However, as you will see in this tip, the LARGE and the SMALL functions are exceedingly easy to use and provide greater flexibility and power than their MAX and MIN counterparts.
Using the large and small functions
The LARGE function identifies the nth largest item in a range. On the other hand, the SMALL function identifies the nth smallest item in a range. To illustrate, you could create the following formula to identify the largest value in the range of F2 through F2000: =LARGE(F2:F2000,1)
The reference to “1” near the end of the formula instructs Excel to identify the single largest value in the range. However, if you needed to identify the second largest value in the range, you could simply replace the “1” with a “2”: =LARGE(F2:F2000,2).
Similarly, to identify the single smallest value in the range, you could use the following formula, which uses the SMALL function: =SMALL(F2:F2000,1)
Note that there is no limit on the nth item when utilizing the LARGE and SMALL functions. However, if you specify an nth item that is greater than the total number of records you are examining, then you will receive a #NUM! error message.
summary
As stated above, the LARGE and the SMALL functions provide powerful, yet easy-to-use options to Excel’s MIN and MAX functions. Knowing how to use these two functions provides you with greater flexibility when you need to analyze potentially large volumes of data in Excel. Further, you can analyze your data without using more involved approaches, such as sorting and re-sorting your data or using Dynmaic Arrays. Therefore, carefully consider adding these two functions to your Excel toolbox.
The K2 Enterprises team is proud to offer numerous learning opportunities on Excel and other technology-focused topics. Click here or the button below to learn more about these options.
