Many people who use Microsoft Excel would have no problem figuring out how to sum or average a column of numbers. Indeed, the SUM() and AVERAGE() functions are among the most commonly used. In the following figure, =SUM(E2:E19) or =AVERAGE(E2:E19) can actually be entered using the AutoSum icon or the dropdown next to the AutoSum icon.
It's slightly more difficult to find the largest or smallest number in a column. =MAX() shows the largest value and =MIN shows the smallest value.
But far more difficult is finding the second largest revenue. If you knew only the SUM, AVERAGE, MAX, MIN, COUNT and IF functions, you might try an array formula, such as =MAX(IF(E2:E19=MAX(E2:E19),0,E2:E19)). But then, how would you go about finding the third largest value? This workaround quickly falls apart.
Excel LARGE function to the rescue
The =LARGE(array,k) function can be used 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.
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.
Now that you understand how to use the LARGE function, here are some bonus tips.
Bonus tip 1: Making an exact copy of a formula
Once you've entered the MAX function in J2, there is a ditto shortcut that makes it faster to 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 the right arrow twice to place the cell pointer before the A in MAX. Type IN, followed by Delete Delete to change MAX to MIN.
Bonus tip 2: Avoiding the helper column with 1, 2, 3
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. Here's the benefit: As you copy the formula down, the reference will change to 2:2, 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.
Bill Jelen, aka "MrExcel," has been a Microsoft Excel consultant for over twenty-five years. Read more of his expert Excel tips and tricks at his website, MrExcel.com.
Dig Deeper on ERP System
Have a question for an expert?
Please add a title for your question
Get answers from a TechTarget expert on whatever's puzzling you.