Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Excel tip: Find largest and smallest values in a list

by
17th Jan 2013
Save content
Have you found this content useful? Use the button above to save it to your profile.

In our latest trans-Atlantic Excel tip, David Ringstrom shows how the LARGE and SMALL functions can take some of the drudgery out of finding high/low values in your data.

Ranking the value or performance within an Excel list is a common task. Many people choose to sort data in ascending or descending order, but this tutorial demonstrates how the LARGE and SMALL functions to create an ordered list of the items you want to rank.

Most accountants should be familiar with the MIN and MAX functions in Excel, which return the smallest or largest value within a list.

As we can see here, MIN returns the the smallest value in the list (191) and MAX the largest (958): 

MIN and MAX functions return the largest and smallest values from a list

But both MIN and MAX are limited to the single smallest or largest values. LARGE and SMALL, on the other hand, let you return the second largest or third smallest value if you choose.

As we can see above, the MIN and MAX functions are to the SUM function, except they return the single smallest or largest value 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)

Within these function arguments, array is a range of cells, and k is the nth value you wish to return. In the next screenshot, =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)

 LARGE and SMALL return the nth values from a given list

It can be tedious to manually edit each LARGE or SMALL function manually with the proper value for the k argument if you want to create a list of the top or bottom 10 values. To save time, 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()

Below is what happens when you enter the following formula in cell D2:

 =ROW()-1

In this instance, ROW() would return 2 because it's entered on the second row, so subtracting 1 changes the result to 1. Alternatively, you could put the address of a cell in row 1 of the worksheet:

=ROW(D1)

Use INDEX and MATCH functions to create a ranked list without re-sorting the source data

In cell F2, enter the formula:

=LARGE(B$2: B$11,D2)

Depending on the situation, you could use this formula instead:

 =LARGE(B$2: B$11,ROW()-1)

The dollar signs in the formula are absolute references that tell Excel not to change the row numbers when the formula is copied down the column.

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))

MATCH and INDEX are worthy of further investigation in their own right, but 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 can 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. In that situation you would need to use the COUNTIF function to create a tiebreaker that will give you a unique value to match for each item in the list. We’ll tackle this in the next Excel tip. 

Once the formulae are copied from cell D2 through F2, copy them down as many rows as needed without any additional modifications, as shown below. Note that if you drag too far, LARGE or SMALL will return #NUM!.

F2 too far, LARGE will return #NUM!.

"Either you work Excel, or it works you!" says David Ringstrom CPA, the head of Atlanta-based software and database consultancy Accounting Advisors. He presents Excel training webcasts for CPE Link and contributes articles on Excel to AccountingWEB and Microsoft Professional Accountant's Network newsletter. He can be reached by email at david[AT]acctadv.com. You can find further ExcelZone tutorials from David H Ringstrom here.

Tags:

Replies (3)

Please login or register to join the discussion.

avatar
By brian.barrett
21st Jan 2013 02:41

Beware Row Number Changing

LARGE and SMALL look great functions that I have never actually used so thanks for this information.

In the example given, however, and possibly in order to keep the example as specific to the functions as possible, there is the danger that should new rows be inserted above the range B2:B11, then the example will not work.  This is because ROW()-1 will not return numbers in the range 1-10.  If the example is in use by the spreadsheet author, then it would be easy to amend.  The author, however, may not be the user and we are all in danger of users 'prettying up' the sheet such as adding a heading.

I would therefore recommend that we replace the =row()-1 to =row()-row(B$2)+1.  As said in the example, the $ in B$2 will mean that the formula will remain the same as it is copied down the column.  Should a heading be added above row 2, then the formulae will automatically change to B$3.  Equally you can actually use the range and the row function will return the first cell in the range i.e. = row()-row(B$2:B$11)+1.  This may give the formula the same feel as the other formulae.

I may be a little [***] with this comment, but when designing spreadsheets my first thought is 'How can anyone [***] this up', and to design accordingly.

Thanks (4)
avatar
By Rbryant
23rd Jan 2013 11:56

is it really needed?

Why does D2:D11 need to be a formula in the first place? If you're looking for the top 10, or more, just type in the numbers (or Edit-Fill-Series if typing is too much!).

Then insertion of rows above has no impact

Thanks (0)
avatar
By brian.barrett
23rd Jan 2013 12:25

Possibly Needed - Possibly Not

Examples shown in this type of blog have often been heavily simplified to show the main points the author is trying to get across.

In this example it would be very easy for the author to keep modifying the sheet so that the range D2:D11 range has the numbers 1 - 10 in it.  The author could do this should a heading be added, or should another fruit be added and the range be expanded.

The sheet, however, may be used by someone other than the author.  The author may give information on how to add fruit, or the fruit may be added by some vba code.  One advice that may be given to add fruit may be to copy a row, and then use the 'Insert Copied Cells' option from the click-right-button list of options.  In this case the user only has to then change the name of the fruit and the sales.  The analysis information will change automatically.

A further reason for having the formula is that, in general, sheet developers generally try and have an area where data is entered, and a results/analysis area.  In general you would not wish to keep changing the data in an analysis area of the spreadsheet and would prefer to have formula that will cope with all anticipated changes/data in the sheet.

As stated, though, whether the formula is needed or not will depend on the complexity of the sheet, who will be using it, and whether the developer wishes to keep a distinction between data and analysis areas of the spreadsheet (which itself is likely to be linked to the sheet's complexity).

Thanks (0)