Q

Finding the top three values using the Excel LARGE function

Microsoft Excel consultant Bill Jelen, aka MrExcel, shows how to find the kth largest number in a spreadsheet column using the Excel LARGE function.

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.

AutoSum Icon
Figure 1

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.

=MAX and =MIN
Figure 2

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.

What now?

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.

Second and third largest values
Figure 3

The analogous function for the three smallest values is the =SMALL function, as shown in Figure 4.

=SMALL function
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.

Second largest value
Figure 5

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.

This was first published in January 2014

Dig deeper on ERP System

Pro+

Features

Enjoy the benefits of Pro+ membership, learn more and join.

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

SearchManufacturingERP

SearchSAP

SearchOracle

SearchBusinessAnalytics

SearchContentManagement

SearchDataManagement

SearchCRM

Close