LO Paste v Av $ % 9 v G20 x 1 1 Average Classification 1 Classification 2 2 3 7 4 5 3 6 7 R 10 Employee Name Joe D. Mikey D. Joan J. 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 K. Ann H. Nadia C. B 1st review 2nd Review 3rd Review 6 7 8 6 7 9 9 10 4 5 5 6 7. 9 10 7 10 7 5 2 4 3 10 10 9 7 5 9 6 8 8 7 5 7 10 9 9 3 4 3 5 6 7 9 10 10 10 7 5 4 5 4 7 10 9 5 5 9 13 12 13 14 15 16 17 18 19 20 21 22 23 Above Average Below Average 24 25 26 27 28 Line Cha Lookup Breakeven Analysis 3 per-e Income Statement Pivot Table Data Ready G20 v fx $ % Average Classification 1 Classification 2 6 8 9 10 11 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 K. Ann H. Nadia C. D 1st review 2nd Review 3rd Review 6 7 8 7 6 7 9 9 10 3 4 5 5 6 7 9 10 7 10 7 5 2 3 10 10 9 7 5 9 6 8 8 7 7 10 9 9 3 4 3 5 6 7 9 10 10 10 7 5 4 5 4 9 10 7 5 5 9 12 13 14 15 16 17 18 19 20 2.1 22 23 Above Average Below Average 24 25 26 27 28 29 20 Line Breakeven Analysis Lookup Income Statement pur-e Pivot Table Data Ready EXERCISE 5: USING "LOOKUP" AND "COUNTIF FUNCTIONS TO RANK EMPLOYEE PERFORMANCE 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 performance ratings . In the 5 Column 6 Column E, calculate 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 0 to 4.9 is the classification of "Poor" 5 to 7.9 is the classification of "Average". Sto 10 is the dassification 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. 1. 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 kerge the following results in cells B23 and B24 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 word "Above average than "Below Average using 5 as the cut point. Redefine the LOOKUP formula so that appealmately equal number of employees are classified as "Above erage and usBelow Average." 6. What is the new cutoff point
