**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 as “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,9.1},{"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. - 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?” The image displays a spreadsheet titled "Lookup" showing employee performance reviews. The data is organized into columns and rows as follows: **Columns:** 1. **Employee Name**: Names of employees being evaluated. 2. **1st Review**: Scores from the first review session. 3. **2nd Review**: Scores from the second review session. 4. **3rd Review**: Scores from the third review session. 5. **Average**: This column is intended to calculate the average score of the three reviews, but it is currently empty. 6. **Classification 1** and **Classification 2**: These columns are labeled but contain no data or classifications. **Rows (Sample Data):** - **Joe D.**: 1st Review - 6, 2nd Review - 7, 3rd Review - 8 - **Mikey D.**: 1st Review - 7, 2nd Review - 9, 3rd Review - 7 - **Joan J.**: 1st Review - 9, 2nd Review - 9, 3rd Review - 10 [Additional names and scores continue in a similar format.] **Notes:** - There is space at the bottom labeled "Above Average" and "Below Average," suggesting future categorization based on calculated averages. - The spreadsheet includes a toolbar with common Excel functions such as formatting, sorting, and data analysis tools. - Tabs at the bottom hint at other sections of the file: Pivot Table Data, Income Statement, Break-even Analysis, Line Chart, and Bar Chart. The spreadsheet appears to be a tool for analyzing employee performance over three review cycles, with a structure in place for calculating averages and categorizing employees based on their scores.