6. Ranking Data Records

 
Subtitles Enabled

Sign up for a free trial to access more free content.

Free trial

Overview

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.

Summary

  1. 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.

  2. 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.

Transcript

Excel Excel for Business Analytics Learning Plan
Lookups and Database Functions

Contents

My Notes

You can take notes as you view lessons.

Sign in or start a free trial to avail of this feature.

Free Trial

Download our training resources while you learn.

Sign in or start a free trial to avail of this feature.

Free Trial