Save the beginning file (MakeupExam2.xlsx) as LastnameFirstname_MakeupExam2.xlsx where Lastname and Firstname are your last and first name.
computer science
Description
Save the beginning file (MakeupExam2.xlsx) as LastnameFirstname_MakeupExam2.xlsx where Lastname and Firstname are your last and first name. When you are finished, send your makeup assignment to your lab instructor for grading. This assignment provides additional work to improve score on exam 2. Note that the makeup assignment has possible 130 points. Your score on the makeup assignment will be combined with your score on exam 2. The highest combined score is 75. If you did not take exam 2 or you submitted the exam file incorrectly, your highest score will be 65. Question 1) answer the following questions by using conditional summary functions (20 points). a) Make a table with cells A1 to I21 with name EmpTable, a heading row, banding rows, no total row, and no filter buttons. Select an appropriate table style. (2 points) b) Write a function in cell A23 to return the number of employees with VP title and greater than 10 years of experience. Use structured references to refer to columns in the table. (5 points) c) Write a function in cell B23 to return the average salary of employees with VP Title. Use structured references to refer to columns in the table. (4 points) d) Write a function in cell C23 to return the average years of experience of employees whose salaries are greater than $100,000. Use structured references to refer to columns in the table. (4 points) e) Write a function in cell D23 to return the number of employees with greater than 5 and less than or equal 10 years of experience Use structured references to refer to columns in the table. (5 points) Question 2) write a nested If function in cell F2 to return “Low”, “Average” or “High” based on Employees’ Years of Experience. You can find the criteria in the following table. Use structured references to refer to columns in the table. (10 points)
