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

» Register now

The full article is available to registered AccountingWEB members only. To read the rest of this article you’ll need to login or register.

Registration is FREE and allows you to view all content, ask questions, comment and much more.

Comments

Beware Row Number Changing    4 thanks

brian.barrett | | Permalink

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?

Rbryant | | Permalink

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

Possibly Needed - Possibly Not

brian.barrett | | Permalink

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