# Excel tip: Find largest and smallest values in a list

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.

Continued...

Tags

### Beware Row Number Changing 4 thanks

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 anal with this comment, but when designing spreadsheets my first thought is 'How can anyone bugger this up', and to design accordingly.

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