Website Home Page |

Excel Assignments List

| Froyo Technologies Public Resources

Sample Data

| Employee ID | Department | Evaluation Date | Rating |

|-------------|------------|-----------------|--------|

| 1           | HR         | 2023-01-01      | 4      |

| 2           | Sales      | 2023-01-05      | 5      |

| 3           | Finance    | 2023-01-08      | 3      |

| 4           | HR         | 2023-01-01      | 4      |

| 5           | Sales      | 2023-01-05      | 5      |

| 6           | Finance    | 2023-01-08      | 3      |

| 7           | HR         | 2023-01-01      | 4      |

| 8           | Sales      | 2023-01-05      | 5      |

| 9           | Finance    | 2023-01-08      | 3      |

 

Task :

1. Create a table to store employee evaluation data with columns for Employee ID, Department, Evaluation Date, and Rating.
2. Calculate the average rating for each department using AVERAGEIFS function.
3. Use VLOOKUP function to retrieve the Department of Employee ID 2.
4. Calculate the year-over-year change in average rating using INDEX and MATCH functions.
5. Create a radar chart to visualize the ratings across different departments.

To perform the tasks outlined in Assignment  using Excel, follow these step-by-step instructions:

Steps to Perform:

1. Create a Table to Store Employee Evaluation Data:
   • Set up a table with columns for Employee ID, Department, Evaluation Date, and Rating.
   • Populate the table with sample data provided.
2. Calculate Average Rating for Each Department Using AVERAGEIFS Function:
   • Use the AVERAGEIFS function in Excel to calculate the average rating for each department.
   • Example: =AVERAGEIFS(Rating_column, Department_column, "HR") assuming Rating is in column D and Department in column B.
3. Use VLOOKUP Function to Retrieve Department of Employee ID 2:
   • Use the VLOOKUP function to find and retrieve the Department for Employee ID 2.
   • Example: =VLOOKUP(2, A2:D4, 2, FALSE) assuming Employee IDs are in column A and Departments in column B.
4. Calculate Year-over-Year Change in Average Rating Using INDEX and MATCH Functions:
   • Calculate the year-over-year change in average rating for a specific department using INDEX and MATCH functions.
   • Example: =INDEX(Average_rating_range, MATCH(current_year, Year_column, 0)) / INDEX(Average_rating_range, MATCH(previous_year, Year_column, 0)) - 1 where Average_rating_range is the range of average ratings over years, and Year_column is the column containing evaluation years.
5. Create a Radar Chart to Visualize Ratings Across Different Departments:
   • Select the data range including Departments and Ratings.
   • Go to Insert > Radar Chart > Radar to create the chart.
   • Customize the chart with appropriate titles, axis labels, and legend to visualize ratings across different departments.

Detailed Steps:

• Step 1: Create Table for Evaluation Data
  • Create a table with headers for Employee ID, Department, Evaluation Date, and Rating.
  • Enter sample data into the table.
• Step 2: Calculate Average Rating for Each Department
  • Enter =AVERAGEIFS(D2:D4, B2:B4, "HR") assuming Rating is in column D and Department in column B. Copy this formula for other departments.
• Step 3: Use VLOOKUP to Retrieve Department of Employee ID 2
  • Enter =VLOOKUP(2, A2:D4, 2, FALSE) assuming Employee IDs are in column A and Departments in column B.
• Step 4: Calculate Year-over-Year Change in Average Rating
  • Calculate the year-over-year change in average rating using formulas involving INDEX and MATCH functions to locate specific years and ratings.
• Step 5: Create Radar Chart to Visualize Ratings
  • Select the data range of Departments and Ratings.
  • Go to Insert > Radar Chart > Radar.
  • Customize the chart with appropriate titles, axis labels, and legend to present ratings across departments effectively.