80.2K

Verified Solution

Link Copied!

Using the results, do the following: 1. List the names of the Best employees 2. List the names of the Near Best employees 3. List the names of the Below Best employees 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 7th Column, or Column G. Next, were going to count how many employees were categorized as either Above Average or Below Average. Place the following results in cells B23 and B24. 4. How many people scored Above Average? 5. How many people scored Below Average? 14 To answer these questions use the COUNTIF function. Its form is =COUNTIF(G2:G21, Above Average), where G2:G21 is the range of cells. In this example, the function counts the number of employees that were Above Average according to the former classification. Lastly, you will notice that more people scored Above Average than Below Average using 5 as the cut point. Redefine the COUNTIF formula so that a roughly equal number of employees are classified as Above Average as Below Average. 6. What is the new cutoff point?

Write down the EXACT formula you used for columns 6 and 7 as well as for the answers to all the other questions.

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 "Bus 1011 Excel Student Spreadsheets" and click on the tab/worksheet labeled "Lookup Table". There you will find the performance ratings-on a ten-point scale-for 20 employees. Each employee has 3 performance ratings. In the 5th Column, or Column E, calculate the average performance rating for each employee to one decimal place. Then, in the 6th Column, or Column F, use the Lookup formula below to classify the employees. o =LOOKUP(E2,{0,4,9,10},{"Below Best", "Near Best", "Best"}) The Lookup formula is structured as follows: E2 is the Cell containing the data for which the calculation should be made. Oto 4 is the classification of "Below Best". 4 to 9 is the classification of "Near Best". 9 to 10 is the classification of "Best". The words "Below Best", "Near Best", "Best" are placed for each of the three classifications according to an employee's average rating. 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 "Bus 1011 Excel Student Spreadsheets" and click on the tab/worksheet labeled "Lookup Table". There you will find the performance ratings-on a ten-point scale-for 20 employees. Each employee has 3 performance ratings. In the 5th Column, or Column E, calculate the average performance rating for each employee to one decimal place. Then, in the 6th Column, or Column F, use the Lookup formula below to classify the employees. o =LOOKUP(E2,{0,4,9,10},{"Below Best", "Near Best", "Best"}) The Lookup formula is structured as follows: E2 is the Cell containing the data for which the calculation should be made. Oto 4 is the classification of "Below Best". 4 to 9 is the classification of "Near Best". 9 to 10 is the classification of "Best". The words "Below Best", "Near Best", "Best" are placed for each of the three classifications according to an employee's average rating

Answer & Explanation Solved by verified expert

See Answer