+ Reply to Thread
Results 1 to 6 of 6

Top 5 and Lowest 5 Performers (based on criteria)

  1. #1
    Forum Contributor
    Join Date
    05-29-2017
    Location
    UK
    MS-Off Ver
    Pro Plus 2016
    Posts
    301

    Top 5 and Lowest 5 Performers (based on criteria)

    Hi, can't get my head around this one...

    B C D E
    2 Name Contacts Responses Rate
    3 Name1 100 80 80%
    4 Name2 200 100 50%
    5 Name3 300 150 50%
    6 Name4 1,000 700 70%

    Long list down....

    How do I get a formula to return Top 5 (% Rate) performers based on highest number of Contacts and % Rate? Basically, 1 Contact with 1 Response (100% Rate) would not be equal Performer to 1,000 Contacts with 800 Responses (80% Rate)

    So in above example top 3 would be Name4, Name3 & Name1 (would still need to return 5, but using 3 here just t save typing)

    Also, same thing only to return Lowest 5 performers?

    Is there a way?

    Thanking in advance!

  2. #2
    Forum Contributor
    Join Date
    05-29-2017
    Location
    UK
    MS-Off Ver
    Pro Plus 2016
    Posts
    301

    Re: Top 5 and Lowest 5 Performers (based on criteria)

    B=Name, C=Contact, D=Responses, E=Rate

    2, 3, 4, 5, 6 are the Rows

  3. #3
    Forum Contributor
    Join Date
    07-13-2015
    Location
    Quebec, Canada
    MS-Off Ver
    2013
    Posts
    232

    Re: Top 5 and Lowest 5 Performers (based on criteria)

    What is the logic behind the list? Why is Name4 better than Name1 if its rate is higher?

  4. #4
    Forum Contributor
    Join Date
    05-29-2017
    Location
    UK
    MS-Off Ver
    Pro Plus 2016
    Posts
    301

    Re: Top 5 and Lowest 5 Performers (based on criteria)

    Sorry for late reply. Basically, 1 Contact with 1 Response (100% Rate) would not be equal Performer to 1,000 Contacts with 800 Responses (80% Rate)

    I mean, when we look in reviews online, do we trust 100% rate coming from 3 people, or 80% rate coming from 1,000 reviews? Same here, I need to filter names with highest contact number + highest % rate. Possible?

  5. #5
    Forum Contributor
    Join Date
    03-22-2017
    Location
    Malaysia
    MS-Off Ver
    Excel 2010
    Posts
    230

    Re: Top 5 and Lowest 5 Performers (based on criteria)

    If you mean to top to bottom should be based on % rate,and if it same it go to quantity.

    For Top Five,
    Then you can use below ARRAY formula,
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    For Bottom 5
    Then you can use below ARRAY formula,
    Formula: copy to clipboard
    Please Login or Register  to view this content.


    PS: it just show the rank (ie 1, 2, 3,..., 10,...), for top 5 or bottom, you need to filter or sort. For more convenience, you may use the conditional formatting, there got highlight for top 5 and bottom five apply to this two formula.

    ARRAY formula is need to confirm by Ctrl + shift + Enter instead of Enter.

    follow these step if you not understand,
    1. paste the formula in
    2. double click the cell
    3. Press Ctrl + shift + Enter
    Last edited by BoredWorker; 06-25-2017 at 04:18 AM.
    Hope you can learn every time you visit here.

    If you still confuse on how it work, kindly ask or go to
    i) Formula - Formula (Ribbon) > Formula Auditing (Section) > Evaluate Formula > Evaluate; or
    ii) VBA/Code - Click F8 to see how it work step by step.

    It it take care of your question, Please:
    Mark tread as [Solved] [Thread Tools->Mark thread as Solved]
    ;and
    Click *Add Reputation to thank anyone solved your question.

  6. #6
    Forum Contributor
    Join Date
    05-29-2017
    Location
    UK
    MS-Off Ver
    Pro Plus 2016
    Posts
    301

    Re: Top 5 and Lowest 5 Performers (based on criteria)

    Awesome, thank you so much!! Appreciate your help

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Array formula to list top performers ignoring blanks
    By Shellybelly in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-05-2015, 10:27 AM
  2. How do I automate color coding the highest performers on a chart?
    By worthm in forum Excel Charting & Pivots
    Replies: 4
    Last Post: 03-19-2015, 06:49 PM
  3. Need to Return Lowest Price based on Criteria
    By BYizz in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-23-2013, 01:03 PM
  4. Replies: 1
    Last Post: 11-09-2012, 12:19 PM
  5. Finding the lowest value based on multiple text criteria
    By meherenow9 in forum Excel General
    Replies: 14
    Last Post: 09-16-2011, 02:01 PM
  6. Creating a Top Performers Table
    By freeman in forum Excel General
    Replies: 29
    Last Post: 04-25-2010, 11:53 AM
  7. Grab lowest date based on criteria
    By SeanKosmann in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 04-06-2010, 04:36 PM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1