Many people who use Microsoft Excel have no problem figuring out how to add or average a column of numbers. Indeed,...
the SUM() and AVERAGE() functions are among those most commonly used.
As an example, you can use the AutoSum icon or the drop-down next to the AutoSum icon to enter =SUM(E2:E19) or =AVERAGE(E2:E19), as shown in Figure 1.
AutoSum can only be used if the highlighted cells are in a contiguous row or column.
It's more difficult to find the largest or smallest numeric value in a column, however. This is especially true if the cells aren't in a contiguous row or column.
Use =MAX() and =MIN() to find the largest and smallest values in the range, respectively. Create the desired range by putting the cell numbers in the parentheses.
Figure 2 demonstrates how to find the largest and smallest values in Figure 1's Revenue column using =MAX() and =MIN(). The =MAX() value is E2:E19 -- the cells at the top and bottom of the Revenue column.
The program sorts through the cells sandwiched between E2 and E19 to determine which numeric value is the largest. Similarly, the =MIN() value is also E2:E19, except the program is sorting through the cells to find the smallest number.
Finding the second-largest revenue is far more challenging, however.
If you only know the SUM, AVERAGE, MAX, MIN, COUNT and IF functions, you could try an array formula, such as =MAX(IF(E2:E19=MAX(E2:E19),0,E2:E19)). But how would you go about finding the third-largest value? This workaround is difficult and quickly falls apart.
Excel LARGE function to the rescue
Use the =LARGE(array,k) function to return the largest, second-largest, third-largest and kth largest values from a range.
To set up the formulas, first build a helper column with the numbers 1, 2 and 3, as shown in K6:K8 in Figure 3.
Helper columns simplify complex formulas or difficult operations. You can use helper columns to sort, perform lookups with multiple criteria or in conjunction with formulas that return values for rows that meet specific conditions.
In this instance, the helper column is used to return the largest value.
In J6, use =LARGE($E$2:$E$19,K6). This will return the largest value from E2:E19. This is identical to using the MAX function. However, when you copy the formula from J6 to J7 and J8, the formula will return the second-largest and third-largest values from the range.
The analogous function for the three smallest values is the =SMALL function, as shown in Figure 4.
Make an exact copy of a formula
Once you've entered the MAX function in J2, use a ditto shortcut to more quickly enter the MIN function. Go to cell J3. Press Ctrl+' to make an exact copy of the formula in J2 in J3 with the cell in edit mode.
To quickly move to the beginning of the formula, press F2, Home, then double-click the right arrow to place the cell pointer before the A in MAX. Type IN, followed by Delete, Delete to change MAX to MIN.
Avoiding the helper column with 1, 2, 3
If you'd rather not use a helper column, you can work without it.
Each subsequent LARGE formula requires a higher value for the k argument. To automatically have the k argument increase as you copy down, use ROW(1:1) in the first formula. Of course, 1:1 is a reference to row 1. The ROW(1:1) is 1. As an added benefit, the reference will change to 2:2 as you copy the formula down, which returns the second-largest value and so on.
While LARGE and SMALL are obscure, they're a great pair of functions for finding the top three, top five, top 10 or bottom N values from a range.
Avoid formulas with pivot tables
If you'd like to avoid using formulas altogether, use a pivot table. These are reporting engines built into Excel that analyze data without using formulas. You can create a pivot table using a relatively quick and simple process, especially if your source data is well-structured. Use good source data that is organized in a tabular layout. This helps prevent problems in the future and will make creating and maintaining the pivot table easier.
To create one, first click on the Insert tab, then click on the PivotTable button. Once in the Create PivotTable dialog box, select the data you wish to use and then click OK. Drag a "label" field into the Row Labels area, then drag a numeric field into the Values area. Once you've created the table, you can program it to display data in whatever order you want.
Bill Jelen, also known as "MrExcel," has been a Microsoft Excel consultant for 25 years. Read more of his expert Excel tips and tricks at his website, MrExcel.com.