Sort and Rank a list using formulas

Instructions: You can type values or formulas into cells above but to access Excel functionality, or if it is easier to work directly in Excel, open the file by clicking on the Excel Icon at the bottom.

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

In the Topic Example workbook, we have a list of sales and we want to be able to do some analysis on the scale of quantities sold. The MAX and MIN functions will give us the highest and lowest quantities sold but what if we wanted to know this on a bit more of a granular level – e.g. getting a list (and total) of the top 5 sales or knowing what the third highest sale in that period was? And suppose for any given sale, we wanted to know how it ranks in the list? Some of this analysis we could do by applying filters/sorts to our data and then manually counting/summing but Excel actually provides functions which allow you to sort a list in ascending or descending order and to provide a ranking for any given number in a list. Combining this with some other functions (such as OFFSET) we can yield some powerful analysis such as obtaining the sum of the largest X values.

The SMALL and LARGE formulas are really an extension of the MAX and MIN functions. The MAX function gives you the largest value in a range whereas the LARGE function gives you the nth largest value; the MIN function gives you the smallest value in a range whereas the SMALL function gives you the nth smallest value.

The syntax of the MIN function for example is =MIN([range]), so to find the smallest value in our list of sales (in cells $D$2:$D$9) we use =MIN($D$2:$D$9) which returns the value of 100. The syntax of the SMALL function is =SMALL(array,k) where ‘array’ is just the range we want to look in and k represents finding the kth smallest value. So =SMALL($D$2:$D$9,1) gives us the lowest value in the range (i.e. the same as the MIN function), =SMALL($D$2:$D$9,2) gives us the second lowest value and so on. The same applies for the MAX and LARGE functions: =MAX($D$2:$D$9) and =LARGE($D$2:$D$9,1) both give us the largest value in the range, and =LARGE($D$2:$D$9,2) gives us the second largest value in the range.

We can then use this to obtain an ordered list of our sales – using SMALL to list it in ascending order and LARGE to list it in descending order. So we want the formula in the first item of our ascending list to be =SMALL($D$2:$D$9,1) and the second item to be =SMALL($D$2:$D$9,2) and so on. Sounds easy enough – the only slight annoyance with this is that when we write =SMALL($D$2:$D$9,1) and drag the formula down, the 1 at the end doesn’t change to 2, 3 etc. To get around this, rather than writing 1 in the formula, we write Row(1:1) so that our formula becomes =SMALL($D$2:$D$9,ROW(1:1)). As we drag this down, the Row(1:1) changes to Row(2:2), Row(3:3) and so on to give us the second/third/nth smallest value in the range. The other thing we need to be careful of is that when we try to obtain the nth smallest/largest item for a n that is greater than the number of items in the list (e.g. we try and obtain the 11thsmallest value in a list that only has 10 items) the formula will return a #NUM error. We can get around this by error trapping using the IFERROR function so that if the formula evaluates to an error, it returns perhaps a more slightly empty cell instead. Our formula then becomes =IFERROR(SMALL($D$2:$D$9,ROW(1:1)),””). Of course if we want to replicate an entire list (but just sort it in ascending/descending order) we also need to make sure that we drag our SMALL/LARGE formula down far enough to capture the number of items in the list.

Once we have our list in ascending/descending order (either by using the SMALL/LARGE formula or just manually sorting it), if we then want to obtain the sum of the X largest/smallest values we have to combine it with the OFFSET function – see the topic on Refer to a range dynamically for an understanding of how this function works. We need to use the height/width parameters of this function to extend the first cell in our ordered list by a specified number of rows. For example if our first (descending order) list item was in cell G19 and we wanted to get the sum of the 5 largest values we would use the OFFSET function to return the range that is cell G19 extended by 5 rows, i.e. G19:G23, and then sum this. So = SUM(OFFSET(G19,,,5)) equates to =SUM(G19:G23) which is the sum of the 5 largest values.

If instead of ordering the list you just wanted to know for each item in the list where it ranks relative to other items (e.g. whether it is the 2nd highest or 10th highest) then you can use the RANK function in Excel. The syntax of the RANK function is =RANK(number, ref, [order]) which sounds quite confusing but the application of it is actually very simple. To rank the cell D2 in the range $D$2:$D$9 we use the function =RANK(D2,$D$2:$D$9). This returns a value of 8 indicating that in the range $D$2:$D$9 the value in D2 of 100 is the 8th highest value (which is actually the lowest value).

The ‘order’ parameter of the RANK function allows you to set whether you want to rank items in descending order (so that low numbers have a low ranking – this is the default) or ascending order (so that low numbers have a high ranking). Setting the order parameter to 0 or leaving it blank sets it to descending order; setting it to 1 puts a rank according to an ascending order.

In the Topic Example Workbook, fill in the yellow cells as follows:

• In cell D12 write a MIN formula to return the lowest quantity sold in the range D2:D9, and then return the same result using the SMALL function in cell D13
• In cell D15 write a MAX formula to return the lowest quantity sold in the range D2:D9, and then return the same result using the LARGE function in cell D16
• In the range A19:A36 replicate the quantity sold range (D2:D9) but in ascending order, using the SMALL and ROW functions. Use IFERROR to error trap going beyond the number of values in the range
• Repeat in the range G19:G36 but this time sort the sales in descending order using the LARGE function
• In cells J18 and J19 use the sorted lists and combine this with an OFFSET function to work out the sum of the X lowest/highest values where X represents the number in cell H12/H13 respectively
• In cells E2:E9 use the RANK function to determine the ranking of each sale with the highest quantity sold getting a rank of 1
• Repeat in cells F2:F9 but this time rank the sales such that the lowest quantity sold gets a rank of 1

A demonstration is shown in the video clip below. Resist the temptation to view the answers file below until you have tried this!

If the file does not display correctly try right clicking in the area above and selecting ‘Refresh’. If you are still unable to view the file properly, or it contains unsupported features, click here to download a copy.

YouTube Video – Excel Magic Trick 616: LARGE & SMALL Functions (8 Examples)