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) Years of experience Experience Level Less than 5 years Low greater than or equal 5 and less than 10 years Medium Greater than or equal 10 years and less than 15 years High Greater than or equal 15 years Very high

Question 3) In cell G2, write a formula using the VLookup function to return the commission rate of employees in 2015 based on their years of experience. You need to use the commission rate look up table. Use structured references to refer to columns in the table. Use absolute reference to the commission rate table. (10 points)

Question 4) Write formulas to determine if the salary of an employee is above or below the average salary for the employees state and employee title. In cell H2 (Average Salary), write a formula to determine the average salary for the employees state and title. You should use a formula with a nested IF function with a comparison on the employees title to determine the third argument in a Vlookup function using the lookup array $M$6:$P$8 matching the employees state to the first column in the lookup array. Here is the first part of the formula with a nested IF function: =IF([@[Employee Title]]=$N$5,VLOOKUP([@[US State]],$M$6:$P$8,2,FALSE), IF( The remaining part should continue the nested IF function for the other two employee titles in cells O5 and P5. (10 points) In cell I2 (Salary Level), write a formula to determine if the average salary is greater than, less than, or equal to the average salary (cell H2). If the salary is greater than the average salary, return Above. If the salary is less than the average salary, return Below. If the salary is equal to the average salary, return Same. (5 points)

Question 5) In cell N18, write a formula to compare the average sales of VPs with High and Medium experience levels using the values in the Experience Level column. If the average sales of VPs with High experience level is greater than VPs with Medium experience level, cell N18 should show High otherwise it should Medium. Use structured references to refer to columns in the table. Use absolute reference to the commission rate table. (10 points)