Finding Top 5 Performers in Excel

I recently came across with a problem asked by one of my friend.  He wanted me to create a function in Excel to know the Top 5 sales performer in his team.  I suggested him a easy way of sorting the data in descending order based on sales figures but he wanted to keep the data intact and know the result.

See the image below to visualize what he was looking for:

To provide him with an answer I used three Excel function namely:

• INDEX(array, row_number, column_number)
• MATCH(value, arrray, match_type)
• LARGE(array, nth_position)

Using these three powerful fucntions in conjunction, I came out with a solution and want to share it with you as well. The formula goes like this

E2: =INDEX($A$2:$A$12,MATCH(LARGE($B$2:$B$12,D2),$B$2:$B$12,0),1)

I dragged the formula till E6 and I got the result required, my Top 5 Sales Performers !

Now let me explain the formula's used here, basically LARGE function helps us in finding k-th largest value in our sales data.  Say fourth or fifth largest sales figure.

Using the value given by LARGE function, we are using MATCH function to know the relative position of the value in our sales figures.

Lastly INDEX function is giving us the name of the Sales Person. That's it !!

I hope you'll get benefitted by using this formula. "HAPPY LEARNING"

SAMPLE FILE

MS Excel: Subtotal Function and Filter

The Subtotal function provide the subtotal of the numbers in a column.  It allows us to perform a calculation in the worksheet, and the same calculation can also be performed on the subsets of the data by applying filters. The magic of this function is that it ignores values in rows hidden by a filter.

The syntax for the Subtotal function is:

        Subtotal(function_num, ref1, ref2, ...)

function_num is the type of subtotal that you'd like to create. Image below explains 11 types which we can select:

ref1, ref2, ... are the ranges of cells that you want to subtotal.

Please go through the example with images below, it will help a lot in understanding Subtotal function.

Here I want to know the average monthly salary for each city. Normal AVERAGE function won't be helpful here because once we'll apply filter to it will still give us the average of all the records in the data. 

Now as per the requirement if I want to know the average monthly salary payable in Augusta region, the SUBTOTAL function will provide me the desired result while AVERAGE function will still give me the average of complete data.

I believe, I've not confused you and this post will be helpful to you while at work.  Let me know your feedback or suggestion.  I SHARE COZ I CARE! "Happy Learning"

Download SAMPLE FILE