Results 1 to 5 of 5

Formula for Creating a "Top Performer" feature on a production report?

Threaded View

h2holbro22 Formula for Creating a "Top... 06-13-2015, 06:54 PM
azumi Re: Formula for Creating a... 06-13-2015, 07:19 PM
h2holbro22 Re: Formula for Creating a... 06-14-2015, 12:12 PM
MarvinP Re: Formula for Creating a... 06-14-2015, 12:38 PM
FlameRetired Re: Formula for Creating a... 06-14-2015, 03:09 PM
  1. #1
    Registered User
    Join Date
    06-13-2015
    Location
    Columbus, Ohio
    MS-Off Ver
    2013
    Posts
    13

    Lightbulb Formula for Creating a "Top Performer" feature on a production report?

    Hello,

    I am the marketing manager for my firm and one of my responsibilities include running daily production reports.
    These production reports include daily performance metrics of all our sales agents.
    I have created a system that weighs each of these metrics and then populates a score accordingly.

    Each agent's performance is graded on a routine basis, using their Score as the main factor (in the process of scrubbing this doc, I removed the main metrics that determine the score, so don't pay attention to the stats).
    To boost motivation for my agents, I want to implement a simple feature on this report that shows the top 3 performers on a daily basis.
    This would appear at the top of the report, as shown below and on the attached file.

    There are two things that I need to know to make this work correctly:

    1. I'm using the following formulas to pull the top 3 scores in the dataset to the appropriate boxes:
    =LARGE(B8:B24,1)
    =LARGE(B8:B24,2)
    =LARGE(B8:B24,3)

    Now I need a formula that can retrieve the agent's name in this same fashion. For example, in the attached doc, the top 3 scores (column B) are 26, 20, and 16. Next to these scores that appear at the top, I need three formulas for cells C2,C3,C4 that would pull over the names that these scores belong to (in this case it would be Shawn R, Michael E, and Brian L.

    2. In the event that any of these agents have the same score, resulting in a tie, there would need to be a way to distinguish the scores to the appropriate agents. For example, if Shawn R. and Michael E. are tied with a score of 26, I would need both of their names to appear in the top performer box instead of just one of them twice. The order of their names is not important, just the fact that they both appear in the top 3.

    Ideally, I can run these reports every day, sort the scores, and have the top 3 producers update accordingly.

    Any ideas?

    I really appreciate any help, it would really be useful for me and my agents.

    Thanks!

    PR.jpg
    Attached Files Attached Files

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 2
    Last Post: 09-28-2013, 02:40 AM
  2. Creating a "report" based on a search
    By tweitzel79 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-20-2013, 04:42 PM
  3. Filter report in pivot table with "greater than" and "less than"
    By gygabyte017 in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 12-29-2012, 08:08 AM
  4. Creating a "report" on results from logical IF's
    By LariRudi in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-10-2012, 01:28 AM
  5. [SOLVED] Activate "sorting" feature when worksheets are setup to "password protect" itself
    By nenadmail in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 07-26-2012, 09:04 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