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.
Ranking our data records
- The LARGE and SMALL functions are a great way of ranking data
- Both accept an array of numbers and the position of the value to be returned
--- array: Range of data you want to check for largest value
--- k: Position (from the largest) in the array of data that you want to return
--- array: Range of data you want to check for smallest value
--- k: Position (from the smallest) in the array of data that you want to return
ALT, I, R: Insert row
F4: Repeat previous command
CTRL + SHIFT + →: Select all cells within data region
CTRL + →: Move to the last cell in the data region
CTRL + Pg Down: Move to sheet on the right
CTRL + Pg Up: Move to sheet on the left
CTRL + SHIFT + ENTER: Create array formula
F4: Anchor cells (when inside formula)
The next feature of our search panel will be a list of the top 5 customers by revenue for each sales rep.
We’ve seen in previous courses how to use the MAX function to find the largest value in an array.
But here we need a function that can also provide us with the second largest, third largest, and so on.
This function is called LARGE.
Let's start off by creating a couple of new rows with the command Alt + I R, and then F4 to repeat the command.
I'll now write Top 5 customers and the numbers 1 to 5 down on the left hand side.
Let's start off by looking at the LARGE function with a very simple example, picking the largest number from this array.
So I'll write “=large”, I'll then select the array, and then I'll select the position that I want to return, which in this case is 1, and this should return 5, which it does.
If I want to return the second largest number, I'll write “=large” again, select the same array, and this time return the second position.
And this gives me the value of 4.
I can also return the smallest value in an array with the function SMALL.
So I'll select the array again, comma 1, and this should return 1, the smallest value in the array.
So as you can see, LARGE and SMALL are simple functions to use and will be ideal for our task of pulling the top 5 customers for each sales rep.
Let's now delete these examples cells and use the LARGE function to pull the top 5 revenue totals for Batiste.
You might remember in the formulas course, we used an IF statement inside a MAX function to return the max value for a given condition.
Here we'll do the exact same thing, except we'll replace MAX with LARGE.
So I'll write “=large” and then I'll create an IF statement that will check to see if the sales person column returns my selected rep.
So I'll select it, and check if it’s equal to selected rep.
If it’s true, then I’d like to return the revenue value, and if it’s false, I'll return an empty string.
I'll then write comma and select the position I’d like to return, which is position 1.
I'll then close the bracket and Ctrl + Shift + Enter to activate the array formula.
And this gives me $41,404.
If I go back inside the formula and anchor the array cells with F4 and press Ctrl + Shift + Enteragain, I can then copy this formula for the remaining 4 customers.
And now I have the top 5 revenue numbers for customers for the rep named Batiste.
If I change the rep, you can see that the numbers will change accordingly.
Our next task is to find the names of these top 5 customers.
This will require us to perform a lookup on two criteria, the sales rep name and the total customer revenue.
I’m going to leave this task until the next lesson, but in the meantime, as an exercise, try to find the top customer revenue for each State in the panel on the right.
I'll leave the answer in the after file below the video.