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!.
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
