Results 1 to 8 of 8

tie scores (stopping/skipping duplicates) using index/match plus small function

Threaded View

  1. #1
    Registered User
    Join Date
    06-03-2016
    Location
    houston, tx
    MS-Off Ver
    2013
    Posts
    11

    tie scores (stopping/skipping duplicates) using index/match plus small function

    First let me start by saying thanks in advance to anyone who can offer a suggestion.

    My problem seems simple or so i thought however i am having issues when using the "index/match" function and trying to rank groups using the "small" function. I have added some screen shots of my report below along with the current formulas i have in place for a more informative question for trouble shooting. I have a report that i use to gather data for the surveys my company has to take. It started as a simple sheet where the data is manually input and it calculates the % score for each team, from there i have a cell that averages each team as an office and on the far right side i have a cell that averages the company as a whole. My Sheet 2 is blank. My sheet three i added a table to show the trending % from day to day and the total of all survey types. Below that i have a table (this is where my problem is) i am building to show the bottom 3 employees from each team and their %, these being the lowest 3 performers. My 4th sheet i labeled "data", on this sheet i built a table with an "averageifs" which takes information from sheet 1 and breaks it down to an employee level, basically showing how they rank individually vs a team on sheet 1.

    Back to the problem now, on my sheet 3 where i am trying to get the lowest 3 performers to populate, i ran a "small" formula to get my % from the data sheet, placing a 1 and a 2 and a 3 in the three cells for my "k". Seems like all works right, however in the column next to it i have an index (match) to index the employees on the data sheet and have them correspond to the scores on the data sheet and the cell next to them on sheet 3. Again seems like it is working, however right now as the example let's say i have an employee with a 100%, and 2 employees with 50% it will give me employee 1, employee 2 and a duplicate of employee2 vs employee 3.

    Essentially i am trying to find a tiebreaker of sorts or not even really that but a way for it to know that when it ranks 1 with a score and it hits the score again to skip the 1 and go to the next. I know using match it is designed to work this way and it's finding the # and assigning it to the first employee on the list each time, but i need it to name each one. Is this possible?

    Below are screen shots of my report along with the formulas used.

    Sheet 1 (really no formula other than a basic average.
    sheet 1.JPG



    sheet 3: this is where i am having the problems with it duplicating one employee with both scores even though it's 2(actually in this example it shows the same person with 3 100's when actually it was three different people you will see on the data sheet
    sheet 3.JPG

    formula being used on sheet 3: on the index it changes as it goes down from F15, F16, to F17 ect. Same on my small, my "k" is 1 then 2 on the next cell, then 3 on the next and so on. these are actually in e15, 16, and 17 for employees and f15, 16 and 17 for %
    sheet 3 e15 column 4 employees formula.JPG
    sheet 3 f15 column 5 employee % formula.JPG

    Data sheet (4) and formula (could only attach 5) (A and B is where i am getting my data for sheet 3) as you see here employees 34584, 35185, and 35221 all have 100's. If you look back on sheet 3 it assigns 34584 to all three vs showing the three different employees.
    data sheet and formula.JPG


    i thought possibly i need a "countif" added but can't figure out how that would work with the formulas. Again any help is greatly appreciated and let me know if you have any questions.

    Thanks
    Jay
    Last edited by Mrjpjones; 06-03-2016 at 08:03 PM.

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Match Index, Small Index with Concatenate
    By kharding15 in forum Excel Formulas & Functions
    Replies: 0
    Last Post: 09-30-2015, 05:38 PM
  2. [SOLVED] Help Requested with Nested Small Function Inside Index/Match Formula
    By trandle in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-26-2015, 10:07 AM
  3. [SOLVED] Index Match Small with duplicate value.
    By n07cn3 in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-15-2014, 07:39 PM
  4. [SOLVED] Top 5 Golf Scores with INDEX/MATCH?
    By anare in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 05-04-2014, 09:02 PM
  5. Index( Match( Small( If
    By TravCAH in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 12-30-2013, 08:41 AM
  6. Match/index/large/small - need for top and low scores
    By Lenisghio in forum Excel General
    Replies: 1
    Last Post: 05-29-2012, 10:13 PM
  7. List all scores which meet criteria (INDEX/MATCH?)
    By Sharpshooter in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 12-01-2010, 08:03 AM

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