Identifying Largest and Smallest Values in an Excel List

By David Ringstrom, CPA

 
Periodically, you may wish to rank a series of items within an Excel spreadsheet. Many users often rely on sorting data in ascending or descending order. I'll describe an alternative that uses the LARGE and SMALL functions to create an ordered list of whatever you'd like to rank.
 
You're probably already familiar with the MIN and MAX functions in Excel, which return the smallest or largest value within a list, respectively. As shown in Figure 1, MIN indicates the smallest value is 191, and the largest value is 958. MIN and MAX are limited to the single smallest or largest values respectively, but LARGE and SMALL allow you to return the second largest or third smallest value if you choose. I'll explain these functions in a moment, but first let's explore MIN and MAX.
 
Figure 1: MIN and MAX return the largest and smallest values from a list, respectively.
 
As shown in Figure 1, the MIN and MAX functions are similar in nature to the venerable SUM function, except they return the single smallest or largest value, respectively, instead of adding up values. The SMALL and LARGE functions work in a similar fashion, but with an extra argument:
 
=SMALL(array,k)
=LARGE(array,k)
 
In these functions, array is a range of cells, and k is the nth value you wish to return. As shown in Figure 2, =LARGE(B2:B11,2) would return 872 as the second largest value, while =SMALL(B2:B11,3) would return 266 as the third smallest value. By way of comparison, the following formulas would both return 958 and 191 for the largest and smallest values, respectively:
 
=MIN(B2:B7)
 
=SMALL(B2:B7,1)
 
=MAX(B2:B7)
 
=LARGE(B2:B7,1)
 
 
Figure 2:  LARGE and SMALL return the nth values from a given list.
 
If you're creating a list of the top or bottom 10 values, it can be tedious to manually edit each LARGE or SMALL function with the proper value for the k argument. To save time, I use the ROW function, either inside the LARGE or SMALL function or in a separate column. The ROW function returns the row number for a given cell. If you enter this in cell D2, Excel will return 2:
 
=ROW()
 
In Figure 3 you can see that I entered this formula in cell D2:
 
=ROW()-1
 
In this case, ROW() would return 2 because it's entered on the second row, so subtracting 1 changes the result to 1. Alternatively, I could provide the address of a cell in row 1 of the worksheet:
 
=ROW(D1)
 
 
Figure 3: These formulas are the basis for creating a ranked list without re-sorting the source data
 
In cell F2, I entered this formula:
 
=LARGE(B$2: B$11,D2)
 
Depending upon my needs, I might have used this formula instead:
 
=LARGE(B$2: B$11,ROW()-1)
 
The dollar signs in the formula instruct Excel not to change the row numbers when I copy the formula down. The last bit of information that you'll likely want is to associate a name with the values that you've isolated. To do so, you can use the MATCH and INDEX functions together in cell E2:
 
=INDEX(A$2:A$11,MATCH(F2,B$2:B$11,0))
 
I'll explain MATCH and INDEX in more detail in an upcoming article, but for now the short answer is that in this case MATCH is determining which row a sales figure amount is on, and then INDEX returns the corresponding text from column A. This is akin to VLOOKUP, but with the flexibility of being able to look up data from the left, which VLOOKUP can't do without making a special provision.
 
There's one caveat to this approach that you should be aware of. If the same value is on your list twice, then MATCH/INDEX will return the corresponding name twice. Next week I'll describe how you can use the COUNTIF function to create a tiebreaker that will give you a unique value to match for each item in the list.
 
In any case, once I have the formulas in cells D2 through F2 in place, I'm able to copy the formulas down as many rows as needed without any additional modifications, as shown in Figure 4. Do keep in mind that if you drag too far, LARGE or SMALL will return #NUM!.
 
 
Figure 4: If you drag the formulas in cells D2:F2 too far, LARGE will return #NUM!.
 
Read more articles by David Ringstrom. 
 
About the author:

David H. Ringstrom, CPA heads up Accounting Advisors, Inc., an Atlanta-based software and database consulting firm providing training and consulting services nationwide. Contact David at david@acctadv.com or follow him on Twitter. David speaks at conferences about Microsoft Excel, and presents webcasts for several CPE providers, including AccountingWEB partner CPE Link.

 

You may like these other stories...

Accountants who specialize in forensic and valuation services point to electronic data analysis, or big data, as the most pressing issue they’ll face in the coming months, according to results of a new survey released...
As complex as federal tax can get, at least you're only dealing with one agency: the IRS. But when you get into state and local sales tax, you're coordinating hundreds of jurisdictions that are constantly changing....
All that was needed on Tuesday was a voice vote for the House of Representatives to pass a bill that would prevent state and local governments from taxing access to the Internet.Now the ball is in the Senate’s court....

Upcoming CPE Webinars

Jul 24
In this presentation Excel expert David Ringstrom, CPA revisits the Excel feature you should be using, but probably aren't. The Table feature offers the ability to both boost the integrity of your spreadsheets, but reduce maintenance as well.
Jul 31
In this session Excel expert David Ringstrom helps beginners get up to speed in Microsoft Excel. However, even experienced Excel users will learn some new tricks, particularly when David discusses under-utilized aspects of Excel.
Aug 5
This webcast will focus on accounting and disclosure policies for various types of consolidations and business combinations.