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)

Question 6) by using an advanced filter, filter the table (Auto_Table) so that it only shows the sedans with manual transmission and less than 100,000 miles. (10 points)

Question 7) after applying advanced filter in Question 6, you need to use appropriate functions to answer the following questions. Use structured references in the formulas

1. Write a function in cell J3 to return the average mileage of the cars (5 points).
2. Write a function in cell k3 to return the sum of the price of the cars (5 points).

Question 8) The table (Auto_Table2) in the Question 8 worksheet shows the inventory of a used car dealer in Denver. Create a pivot table in cell A1 of a new worksheet (Pivot Table) to show the average mileage and the average price of makes based on their body style and year. Year should be nested inside Make on the rows and Body Style on the columns. Add a slicer on Transmission with Automatic selected. Add a clustered line chart below the pivot table. Rollup the Year values so the line chart just shows the Make values, not the Year values. (15 points)

Question 9) This question involves importing a text file and writing text functions to clean and separate components in text. (40 points)

• In the Question 9 worksheet, import the file MakeupExam2PhoneNumbers.txt in cell A1. The text file has headers and comma separators. (2 points)
• In cell C1, type Trimmed Names and then press Enter. In cell C2, use the TRIM function to remove excess spaces from A1. Extend the formula down to cell C11. Resize the column as needed to fit the contents. (3 points)
• In cell D1, type Proper Names and then press Enter. In cell D2, use the PROPER function to use upper case on the first letter of the first name and last name of cell C2. Extend the formula down to cell D11. Resize the column as needed to fit the contents. (3 points)
• In cells E1 to K1, type the following text values, Country Code in E1, Area Code in F1, Prefix in G1, Local in H1, Pos ( in I1, Pos ) in J1, and Pos in K1. Resize the column as needed to fit the contents. Resize the column as needed to fit the contents. (4 points)
• In cell I2, write a formula to determine the position of the ( character in cell B2. Extend the formula to I3:I11. (4 points)
• In cell J2, write a formula to determine the position of the ) character in cell B2. Extend the formula to J3:J11. (4 points)
• In cell K2, write a formula to determine the position of the - character in cell B2. Extend the formula to K3:K11. (4 points)
• In cell E2, write a formula to extract the country code from a phone number. The country code is the digits after the + character and before the ( character. You should use the position of the ( character in cell I2 in the formula. Extend the formula to E3:E11. (4 points)
• In cell F2, write a formula to extract the area code from a phone number. The area code is the digits between the () characters. You should use the position of the ( character in cell I2 and the position of the ) character in J2 in the formula. Extend the formula to F3:F11. (4 points)
• In cell G2, write a formula to extract the prefix from a phone number. The prefix is the digits after the ) character and before the character. You should use the position of the ) character in cell J2 and the position of the - character in K2 in the formula. Extend the formula to G3:G11. (4 points)
• In cell H2, write a formula to extract the local number from a phone number. The local number is the last 4 digits in a phone number. Extend the formula to H3:H11. (4 points)

This is from ISMG 2050