Sign in or start a free trial to avail of this feature.
6. Ranking Data Records
I'd like to extract the top 5 largest customers for each sales rep, and to do this we'll use the LARGE function in Excel.
The LARGE and SMALL Functions (00:04)
We want to find the 5 largest customers for each sales rep. We can do this using the LARGE function, which orders the values in an array from largest to smallest and returns the values at a position we specify. LARGE takes two arguments. First is the array to be searched. Second is the position to be returned. For example, 1 would return the largest value, 2 the second largest value, and so on.
The SMALL function is similar to LARGE. SMALL orders the values in an array from smallest to largest, and returns the value in the specified position.
Finding the Top 5 Customers (01:44)
To find the largest customer for the selected sales rep, we combine LARGE with IF. In the LARGE function, we use an IF statement to find the array argument. The IF statement identifies if the salesperson in the dataset equals the currently selected sales person. We return the revenue value only if this is True. As a result, the LARGE function only considers revenue amounts for the selected salesperson, which is what we want.
This function identifies the revenue amounts for the largest customers of the selected salesperson. The formula adjusts when the salesperson dropdown is changed.