Assignment $1$

Note: First, Solve the questions in Excel. Then copy and paste the functions below each question in this word document. You just need to submit the word document.

Write a function in cell $B2$ to return the first name of Employees. $(10$ points$)$

Write a function is cell $C2$ to return the last name of Employees. $(15$ point.

Write a function in cell E$13$ to return the total sales of Employees. $(5$ points$)$

Write a function in cell $F13$ to return average salary of Employees. $(5$ points$)$

Write a function in cell $H2$ to return the Experience level of Employees. If an Employee has more than $10$ years of Experience, cell H$2$ must show "High", otherwise the cell must be left blank. $(10$ points$)$

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 $Hi\frac{s}{H}er$ sale is greater than $$300,000$ or his$/$her years of experience is greater than $12.(15$ points$)$

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 J$2$ 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$)$

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$).$