EXERCISE 8: 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 Student Spreadsheets BUS 2000" 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 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 function below to classify the employees. =LOOKUP(E2,{0,4,9,10},{"Below Best", "Near Best", "Best"}) o The Lookup formula is structured as follows: E2 is the Cell containing the data for which the calculation should be made. 0 to 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", and "Best" are placed for each of the three classifications according to an employee's average rating. 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, 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 B24. 4. How many people scored "Above Average?" 5. How many people scored "Below Average?" File 5. ¿ E2 4 Undo 5 6 7 8 10 29 Home Insert Draw Page Layout Formulas Data Review 30 Paste Ready X 06 Clipboard V 12 13 14 15 16 17 18 19 20 21 22 23 Above Average 24 Below Average 25 26 27 Calibri :X ✓ fx BIUE G F E A B C D 1 Employee Name 1st review 2nd Review 3rd Review Average Classification 1 Classification 2 Joe D. 6 2 7 8 3 Mikey D. 7 6 7 Joan J. 9 9 10 Ida E. 4 5 Gail S. 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. 3 5 9 10 2 10 7 6 7 10 3 5 9 10 4 9 5 p=r-e Accessibility: Good to go Font 6 10 7 4 ✓11 1585 9 A 6 10 7 5 10 5 A A = = A 7 7 5 3 9 9 8 7 9 Review View Help > 3 7 10 5 4 7 9 Pivot Table Data Income Statement at Lookup ab Wrap Text Alignment Merge & Center H General $ Line Chart Breakeven Analysis % 9 Number I Bar Chart J 10 .00 00 -0 + K 5 Conditional Format as Cell Formatting Table Styles Styles L M N 2 EX Insert Delete Format Cells R Σ AutoSum Fill- Clear 28 0 Sort & Find & Filter ✓ Select Editing ST 11 U Fo Analyze Data Analysis V V W X 81%
EXERCISE 8: 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 Student Spreadsheets BUS 2000" 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 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 function below to classify the employees. =LOOKUP(E2,{0,4,9,10},{"Below Best", "Near Best", "Best"}) o The Lookup formula is structured as follows: E2 is the Cell containing the data for which the calculation should be made. 0 to 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", and "Best" are placed for each of the three classifications according to an employee's average rating. 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, 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 B24. 4. How many people scored "Above Average?" 5. How many people scored "Below Average?" File 5. ¿ E2 4 Undo 5 6 7 8 10 29 Home Insert Draw Page Layout Formulas Data Review 30 Paste Ready X 06 Clipboard V 12 13 14 15 16 17 18 19 20 21 22 23 Above Average 24 Below Average 25 26 27 Calibri :X ✓ fx BIUE G F E A B C D 1 Employee Name 1st review 2nd Review 3rd Review Average Classification 1 Classification 2 Joe D. 6 2 7 8 3 Mikey D. 7 6 7 Joan J. 9 9 10 Ida E. 4 5 Gail S. 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. 3 5 9 10 2 10 7 6 7 10 3 5 9 10 4 9 5 p=r-e Accessibility: Good to go Font 6 10 7 4 ✓11 1585 9 A 6 10 7 5 10 5 A A = = A 7 7 5 3 9 9 8 7 9 Review View Help > 3 7 10 5 4 7 9 Pivot Table Data Income Statement at Lookup ab Wrap Text Alignment Merge & Center H General $ Line Chart Breakeven Analysis % 9 Number I Bar Chart J 10 .00 00 -0 + K 5 Conditional Format as Cell Formatting Table Styles Styles L M N 2 EX Insert Delete Format Cells R Σ AutoSum Fill- Clear 28 0 Sort & Find & Filter ✓ Select Editing ST 11 U Fo Analyze Data Analysis V V W X 81%
Glencoe Algebra 1, Student Edition, 9780079039897, 0079039898, 2018
18th Edition
ISBN:9780079039897
Author:Carter
Publisher:Carter
Chapter10: Statistics
Section10.2: Representing Data
Problem 22PFA
Related questions
Question
I need help with the questions to do the homework of Excel and give the right for me to work each steps which needs to provide or show the formulas?
Expert Solution
This question has been solved!
Explore an expertly crafted, step-by-step solution for a thorough understanding of key concepts.
This is a popular solution!
Trending now
This is a popular solution!
Step by step
Solved in 2 steps with 2 images
Recommended textbooks for you
Glencoe Algebra 1, Student Edition, 9780079039897…
Algebra
ISBN:
9780079039897
Author:
Carter
Publisher:
McGraw Hill
Holt Mcdougal Larson Pre-algebra: Student Edition…
Algebra
ISBN:
9780547587776
Author:
HOLT MCDOUGAL
Publisher:
HOLT MCDOUGAL
Big Ideas Math A Bridge To Success Algebra 1: Stu…
Algebra
ISBN:
9781680331141
Author:
HOUGHTON MIFFLIN HARCOURT
Publisher:
Houghton Mifflin Harcourt
Glencoe Algebra 1, Student Edition, 9780079039897…
Algebra
ISBN:
9780079039897
Author:
Carter
Publisher:
McGraw Hill
Holt Mcdougal Larson Pre-algebra: Student Edition…
Algebra
ISBN:
9780547587776
Author:
HOLT MCDOUGAL
Publisher:
HOLT MCDOUGAL
Big Ideas Math A Bridge To Success Algebra 1: Stu…
Algebra
ISBN:
9781680331141
Author:
HOUGHTON MIFFLIN HARCOURT
Publisher:
Houghton Mifflin Harcourt