Important notes:

If your function doesn't work, remove the equal sign $"="$ from your function to receive partial credit.

Question $1)$ answer the following questions by using the conditional statistical functions: $(20$ points$)$

a$.$ Write a function$/$formula in cell D$24$ to calculate the number of applicants who are from the department of finance or the department of marketing.

b$.$ Write a function$/$formula in cell E$24$ that returns the average GPA of applicants who are from the department of finance or the department of marketing.

Question $2)$ write a nested If function in cell G$2$ to return "Low", "Average" or "High" based on the applicants' industry experience. You can find the criteria in the below table. $(10$ points$)$

$\backslash$table$[[$industry experience,Experience Level$],[$Less than $3$ years,Low$],[$greater than or equal $3$ and less than $5$ years,Average$],[$Greater than or equal $5$ years,High$]]$

Question $3)$ write a Hlookup function in Cell $H2$ to assign the appropriate school based$-$financial aid to the applicants based on their GPA. $(10$ points$)$

Question $4)$ write a function in cell N$22$ to compare the average GPA of domestic and international applicants. If the GPA of the domestic applicants is greater than the international applicants, the function should return domestic otherwise it should international. $(10$ points$)$

Question $5)$ In addition to the school$-$based financial aid, the department of finance and marketing have their own extra budget to offer financial aid to the students based on their GMAT score. Write a function in cell $12$ that returns "not qualified" if the GMAT score of the applicants is below $600$ or they are from Information Systems department. Otherwise, the function should assign the appropriate scholarship to the applicants of each department based on the GMAT score. $(20$ points$)$

Question $6)$ Use an advanced filter to filter the table in a way that it only shows the domestic applicants in the Department of Finance with GPA greater than $80$ and GMAT score greater than $600.(10$ points$)$

Question $7)$ after applying the advanced filter in Question $6,$ write a function in cell K$3$ to return "Yes" if the Average GMAT score of applicants in the filtered table is greater than $650,$ otherwise cell K$3$ should be left blank. $(10$ points$)$

Question $8)$ use a pivot table to show the average GPA and the average GMAT score of all the applicants based on their origin and their

department. $(10$ points$)$