1) Write a function in cell B2 to return the first name of Employees. (10 points) 2) Write a function is cell C2 to return the last name of Employees. (15 points) 3) Write a function in cell E13 to return the total sales of Employees. (5 points) 4) Write a function in cell F13 to return average salary of Employees. (5 points) 5) Write a function in cell H2 to return the Experience level of Employees. If an Employee has more than 10 years of Experience, cell H2 must show "High", otherwise the cell must be left blank. (10 points) 6) Write a function in Cell 12 to return "Eligible" or "not Eligible" based on the following criteria: An employee is eligible for a promotion if His/Her sale is greater than $300,000 or his/her years of experience is greater than 12. (15 points) 7 Write a function in cell J2 to return the new salary of Employees. If the title of Employee is Associate and his sales is less than $200000, his/her new salary is 90% of his/her initial salary (i.e., column F). Otherwise, cell J2 must be left blank. (15 points) Write a function in cell K2 to return High and Low based on the following criteria. Your function must return "High" if the difference between the salary of an employee and the average salary of all employees is greater than $20,000, otherwise your function must return "Low". (10 points) 9) The table in the VLOOKUP worksheet shows that average salary of the employees in the same industry based on their title in three different states. You're going to compare the salary of your employees with those in Texas. To do so, you need to write a function in cell L2 to return the average salary of employees in TX based on their title (10 points). LS Analyst Associate Average Salaries in three satates based on the Employee Title Oklahoma Texas Colorado $65,000 $70,000 $75,000 $90,000 $120,000 $150,000 $75,000 $85,000 $175,000 VP A E H K L LastName Employee Title Years of Experience Salary level Average Salary in Texas #NAME? VP Associate #NAME? FirstName #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! Analyst #NAME? Analyst #NAME? Analyst #NAME? Employee name Abbie ***Brimmer Ciera***Torrance Sara*** Clendenen Brooke*** Caron Jesse***Gilmore Zoe***Brenton Carol***Steward Tracy***Jones Jane***Tuttle Samantha***Stout Riley***Fishback Sales Salary (Initial Salary) $ 281,505 $ 150,205 $ 200,000 $ 140,816 $ 146,775 $ 189,861 $ 325,137 $ 199,746 $ 290,638 $ 53,299 $ 147,404 $ 102,315 $ 100,000 $ 91,258 $ 212,137 $ 79,744 $ 313,035 $ 74,266 $ 305,374 $ 70,832 $ 347,330 $ 189,182 Experience Level Promostion eligibility Adjusted salary not eligi =F2*.90 high not eligi =F2* 90 not eligi =F2*.90 high eligible =F2*.90 high not eligi =F2*.90 not eligi =F2* 90 high not eligi =F2*.90 high not eligi =F2*.90 high eligible =F2*.90 eligible =F2*.90 eligible =F2*.90 #VALUE! Analyst #NAME? #VALUE! Analyst #NAME? #VALUE! Associate #NAME? #VALUE! Associate #NAME? #VALUE! Associate #NAME? #VALUE! Associate #NAME? 1) Write a function in cell B2 to return the first name of Employees. (10 points) 2) Write a function is cell C2 to return the last name of Employees. (15 points) 3) Write a function in cell E13 to return the total sales of Employees. (5 points) 4) Write a function in cell F13 to return average salary of Employees. (5 points) 5) Write a function in cell H2 to return the Experience level of Employees. If an Employee has more than 10 years of Experience, cell H2 must show "High", otherwise the cell must be left blank. (10 points) 6) Write a function in Cell 12 to return "Eligible" or "not Eligible" based on the following criteria: An employee is eligible for a promotion if His/Her sale is greater than $300,000 or his/her years of experience is greater than 12. (15 points) 7 Write a function in cell J2 to return the new salary of Employees. If the title of Employee is Associate and his sales is less than $200000, his/her new salary is 90% of his/her initial salary (i.e., column F). Otherwise, cell J2 must be left blank. (15 points) Write a function in cell K2 to return High and Low based on the following criteria. Your function must return "High" if the difference between the salary of an employee and the average salary of all employees is greater than $20,000, otherwise your function must return "Low". (10 points) 9) The table in the VLOOKUP worksheet shows that average salary of the employees in the same industry based on their title in three different states. You're going to compare the salary of your employees with those in Texas. To do so, you need to write a function in cell L2 to return the average salary of employees in TX based on their title (10 points). LS Analyst Associate Average Salaries in three satates based on the Employee Title Oklahoma Texas Colorado $65,000 $70,000 $75,000 $90,000 $120,000 $150,000 $75,000 $85,000 $175,000 VP A E H K L LastName Employee Title Years of Experience Salary level Average Salary in Texas #NAME? VP Associate #NAME? FirstName #VALUE! #VALUE! #VALUE! #VALUE! #VALUE! Analyst #NAME? Analyst #NAME? Analyst #NAME? Employee name Abbie ***Brimmer Ciera***Torrance Sara*** Clendenen Brooke*** Caron Jesse***Gilmore Zoe***Brenton Carol***Steward Tracy***Jones Jane***Tuttle Samantha***Stout Riley***Fishback Sales Salary (Initial Salary) $ 281,505 $ 150,205 $ 200,000 $ 140,816 $ 146,775 $ 189,861 $ 325,137 $ 199,746 $ 290,638 $ 53,299 $ 147,404 $ 102,315 $ 100,000 $ 91,258 $ 212,137 $ 79,744 $ 313,035 $ 74,266 $ 305,374 $ 70,832 $ 347,330 $ 189,182 Experience Level Promostion eligibility Adjusted salary not eligi =F2*.90 high not eligi =F2* 90 not eligi =F2*.90 high eligible =F2*.90 high not eligi =F2*.90 not eligi =F2* 90 high not eligi =F2*.90 high not eligi =F2*.90 high eligible =F2*.90 eligible =F2*.90 eligible =F2*.90 #VALUE! Analyst #NAME? #VALUE! Analyst #NAME? #VALUE! Associate #NAME? #VALUE! Associate #NAME? #VALUE! Associate #NAME? #VALUE! Associate #NAME
