CIVILUTEE TENTUNIVIRILE Managers often like to rank employees performance. One scheme called "20-70-10," or more disparagingly as "Rank and Yank,"categorizes the top 20 percent of employees as "Best," the middle 70 percent of employees "Near Best," and the bottom 10 percent as "Below Best." Go to the "Excel_Homework and click on the tab/worksheet labeled "Lookup". There you will find the performance ratings-on a ten-point scale--for 20 employees. Each employee has 3 performance ratings In the 5 Column, or Column E, cakulate the average performance rating for each employee to one decimal place. Then, in the 6 Column, or Column F, use the LOOKUP function to classify the employees based on below criteria: o Oto 4.9 is the classification of "Poor". o 5 to 7.9 is the classification of "Average". o 8 to 10 is the classification of "Best". The words "Poor","average", and "Best" are placed for each of the three classifications according to an employee's average rating Using the results, do the following: 1. Highlight the names of the "Best" employees in light blue. 2. Highlight the names of the "Average" employees in light yellow 3. Highlight the names of the "Poor employees in light red. Next, redefine the Lookup function to classify employees as "Above Average and "Below Average based on whether they scored above or below an average of 5 in their three performance scores. Place the results in the Column, or Column G. Next, we're going to count how many employees were categorized as either "Above Average" or "Below Average". Place the following results in cells B23 and 24. 4. How many people scored "Above Average?" 5. How many people scored "Below Average?" To answer these questions, use the COUNTIF function. Lastly, you will notice that more people scored 'Above Average than "Below Average using as the cut point. Redefine the LOOKUP formula se that approximately umber of employees are classified as "Above Average and as low Average 6. What is the new cutoff point? Type All Answers on your Excel Spreadsheet that you will submit LOOKUP(02.10.567,10000 BELOW AVERAGE"," Condo 1 2 2nd Review 7 6 Average Classification 1 H Classification 2 3rd Review 8 7 10 5 6 5 7 7 9 4 6 10 7 B 1st review 6 7 9 3 5 9 10 2 10 7 6 7 10 7 9 5 4 10 Employee Name Joe D. Mikey D. Joan. Ida E Gails Ron A Alex R. Charles M Kate M Debra H John H. John L Paul M. Gloria T Barry R. George W. George H. Carol Ann H Nadiac 3 9 9 12 E10 13 14 10 5 8 5 9 4 8 2 15 9 16 3 5 6 3 7 10 17 18 9 10 4 5 19 10 7 5 10 5 4 20 9 5 22 9 23 Above Average Below Average 24 25 26 27 28 CIVILUTEE TENTUNIVIRILE Managers often like to rank employees performance. One scheme called "20-70-10," or more disparagingly as "Rank and Yank,"categorizes the top 20 percent of employees as "Best," the middle 70 percent of employees "Near Best," and the bottom 10 percent as "Below Best." Go to the "Excel_Homework and click on the tab/worksheet labeled "Lookup". There you will find the performance ratings-on a ten-point scale--for 20 employees. Each employee has 3 performance ratings In the 5 Column, or Column E, cakulate the average performance rating for each employee to one decimal place. Then, in the 6 Column, or Column F, use the LOOKUP function to classify the employees based on below criteria: o Oto 4.9 is the classification of "Poor". o 5 to 7.9 is the classification of "Average". o 8 to 10 is the classification of "Best". The words "Poor","average", and "Best" are placed for each of the three classifications according to an employee's average rating Using the results, do the following: 1. Highlight the names of the "Best" employees in light blue. 2. Highlight the names of the "Average" employees in light yellow 3. Highlight the names of the "Poor employees in light red. Next, redefine the Lookup function to classify employees as "Above Average and "Below Average based on whether they scored above or below an average of 5 in their three performance scores. Place the results in the Column, or Column G. Next, we're going to count how many employees were categorized as either "Above Average" or "Below Average". Place the following results in cells B23 and 24. 4. How many people scored "Above Average?" 5. How many people scored "Below Average?" To answer these questions, use the COUNTIF function. Lastly, you will notice that more people scored 'Above Average than "Below Average using as the cut point. Redefine the LOOKUP formula se that approximately umber of employees are classified as "Above Average and as low Average 6. What is the new cutoff point? Type All Answers on your Excel Spreadsheet that you will submit LOOKUP(02.10.567,10000 BELOW AVERAGE"," Condo 1 2 2nd Review 7 6 Average Classification 1 H Classification 2 3rd Review 8 7 10 5 6 5 7 7 9 4 6 10 7 B 1st review 6 7 9 3 5 9 10 2 10 7 6 7 10 7 9 5 4 10 Employee Name Joe D. Mikey D. Joan. Ida E Gails Ron A Alex R. Charles M Kate M Debra H John H. John L Paul M. Gloria T Barry R. George W. George H. Carol Ann H Nadiac 3 9 9 12 E10 13 14 10 5 8 5 9 4 8 2 15 9 16 3 5 6 3 7 10 17 18 9 10 4 5 19 10 7 5 10 5 4 20 9 5 22 9 23 Above Average Below Average 24 25 26 27 28
