I ONLY NEED HELP WITH QUESTION 6 SHOWING HOW TO USE EXCEL FORMULA WHEN FINDING THE ANSWER: EXERCISE 8: USING “LOOKUP” AN
Posted: Sun Jul 03, 2022 5:22 pm
I ONLY NEED HELP WITH QUESTION 6 SHOWING HOW TO USE EXCELFORMULA WHEN FINDING THE ANSWER:
EXERCISE 8: USING “LOOKUP” AND “COUNTIF” FUNCTIONS TO RANKEMPLOYEE PERFORMANCE Managers often like to rank employeesperformance. 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 thebottom 10 percent as “Below Best.” Go to the “Excel StudentSpreadsheets BUS 2000” and click on the tab/worksheet labeled“Lookup”. There you will find the performance ratings—on aten-point scale—for 20 employees. Each employee has 3 performanceratings. • In the 5th Column, or Column E, calculate the averageperformance rating for each employee to one decimal place. • Then,in the 6th Column, or Column F, use the LOOKUP function below toclassify 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 calculationshould be made. • 0 to 4 is the classification of "Below Best". • 4to 9 is the classification of "Near Best". • 9 to 10 is theclassification of "Best". • The words "Below Best", "Near Best",and "Best" are placed for each of the three classificationsaccording to an employee’s average rating. Using the results, dothe following: 1. List the names of the “Best” employees 2. Listthe names of the “Near Best” employees 3. List the names of the“Below Best” employees Next, redefine the Lookup function toclassify employees as “Above Average” and “Below Average” based onwhether they scored above or below an average of 5 in their threeperformance scores. Place the results in the 7th Column, or ColumnG. Next, we’re going to count how many employees were categorizedas either “Above Average” or “Below Average”. Place the followingresults in cells B23 and B24. 4. How many people scored “AboveAverage?” 5. How many people scored “Below Average?”
To answer these questions, use the COUNTIF function. Itsform is =COUNTIF(G2:G21, "Above Average"), where G2:G21 is therange of cells. In this example, the function counts the number ofemployees that were "Above Average" according to the formerclassification.
Lastly, you will notice that more people scored "AboveAverage" than "Below Average" using 5 as the cut point. Redefinethe LOOKUP formula so that a roughly equal number of employees areclassified as "Above Average" and as "Below Average." 6. What isthe new cutoff point?
EXERCISE 8: USING “LOOKUP” AND “COUNTIF” FUNCTIONS TO RANKEMPLOYEE PERFORMANCE Managers often like to rank employeesperformance. 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 thebottom 10 percent as “Below Best.” Go to the “Excel StudentSpreadsheets BUS 2000” and click on the tab/worksheet labeled“Lookup”. There you will find the performance ratings—on aten-point scale—for 20 employees. Each employee has 3 performanceratings. • In the 5th Column, or Column E, calculate the averageperformance rating for each employee to one decimal place. • Then,in the 6th Column, or Column F, use the LOOKUP function below toclassify 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 calculationshould be made. • 0 to 4 is the classification of "Below Best". • 4to 9 is the classification of "Near Best". • 9 to 10 is theclassification of "Best". • The words "Below Best", "Near Best",and "Best" are placed for each of the three classificationsaccording to an employee’s average rating. Using the results, dothe following: 1. List the names of the “Best” employees 2. Listthe names of the “Near Best” employees 3. List the names of the“Below Best” employees Next, redefine the Lookup function toclassify employees as “Above Average” and “Below Average” based onwhether they scored above or below an average of 5 in their threeperformance scores. Place the results in the 7th Column, or ColumnG. Next, we’re going to count how many employees were categorizedas either “Above Average” or “Below Average”. Place the followingresults in cells B23 and B24. 4. How many people scored “AboveAverage?” 5. How many people scored “Below Average?”
To answer these questions, use the COUNTIF function. Itsform is =COUNTIF(G2:G21, "Above Average"), where G2:G21 is therange of cells. In this example, the function counts the number ofemployees that were "Above Average" according to the formerclassification.
Lastly, you will notice that more people scored "AboveAverage" than "Below Average" using 5 as the cut point. Redefinethe LOOKUP formula so that a roughly equal number of employees areclassified as "Above Average" and as "Below Average." 6. What isthe new cutoff point?