Submit Excel file. Name the file Question 3_your last name. Insert text box to t
ID: 2934319 • Letter: S
Question
Submit Excel file. Name the file Question 3_your last name. Insert text box to type your comment.
The file P02_07.xlsx includes data on 204 employees at the (fictional) company Beta Technologies. Use this dataset to answer questions a, and b.
Use the VLOOKUP function to find the annual salary of employee 15, 70, 150, and 180. [Hint: create a table similar to the one listed below. Use VLOOKUP function to find salary. Help video is available under Lec-6 folder. Excel example is available under Excel Examples in Class folder.
Employee
Annual Salary
15
70
150
180
(b) Create a pivot table and a corresponding pivot chart that shows average of annual salary, broken down by gender. On your pivot table, type “Female” in the place of 1, and “Male” in the place of 0. [Hint: see below:
Gender
Average of Annual Salary
Male
$78,784.71
Female
$65,910.08
Grand Total
$71,274.51
Employee
Annual Salary
15
70
150
180
Explanation / Answer
Row Labels Average of Annual Salary 15 21600 70 68200 150 12500 180 65100 Row Labels Average of Annual Salary 0 78784.70588 1 65910.08403 Grand Total 71274.5098