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
Bookmarks