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 D24 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 E24 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 G2 to return "low", "Average" or "High" based on the applicants' industry experience. You can find the criteria in the below table. (10 points) 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 N22 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) Experience Level School based Finacial aid Department based finaciat ad 75 85 90 95 92 87 77 Applicant la 1 2 3 4 5 6 7 B 9 10 11 12 13 14 15 16 17 18 19 20 Origin Domestic International Domestic Interational International International Interational International International International International Domestic Domestic Domestic Domestic Domestic International Domestic Domestic Domestic Department Information Systems Marketing Finance Marketing Finance Marketing Finance Marketing Finance Information Systems Finance Marketing Information Systems Marketing Finance Information Systems Finance Marketing Information Systems Marketing |||||||||||||=|a 97 85 90 GMAT score 567 553 645 741 680 507 619 720 666 611 691 628 700 723 627 655 631 670 590 589 Years of Industry experience 4 10 10 9 2 2 7 8 2 4 6 5 2 4 4 10 5 8 14 7 86 87 96 77 83 88 89 91 GPA Score Hlook 1 _School Based Finacial aid Look up 80 85 50% of tuition 80% of tuition 100% of tuition 90 pre f Finance finacial aid- Marketing finacial aid- Hlookup 2_Department Based Finacial aid 600 650 700 $1,000 $5,000 $10,000 $2,000 $4,000 $8,000 Domestic or International --> GPA Applicant. Origin Department Domestic Finance 2 internations Marketing 3 Domestic Finance 4 internations Marketing interation Finance 6 Internations Marketing internation Finance internations Marketing 9 international Finance 10 interations Marketing 11 international Finance 12 Domestic Marketing 13 Domestic Finance 14 Domestic Marketing 15 Domestic Finance 16 Domestic Marketing 17 international Finance 18 Domestic Marketing 19 Domestic Finance 20 Domestic Marketing 15 5 S S IS S 3 S 5 $ S s Is S IS S S S S 5 GMAT Score Industry experience 75 S 567 85 IS 553 10 90 S 645 10 955 741 9 92 IS 680 87 IS 607 7715 619 80$ 720 97 IS 666 2 85 IS 611 4 90 IS 691 6 90S 628 5 86 S 700 2 875 723 95 IS 627 4 77 S 655 10 83 S 531 88S 670 895 590 4 913 589 545 619 Applicant Origin Department GPA Domestic Finance $ 2 International Marketing IS Domestic Finance s 4 International Marketing 5 5 International Finance 15 6 International Marketing S 7 International Finance 15 International Marketing $ 9 International Finance 10 International Marketing S 11 International Finance $ 12 Domestic Marketing 15 13 Domestic Finance 5 14 Domestic Marketing 5 15 Domestic Finance 5 16 Domestic Marketing 15 17 International Finance 15 10 Domestic Marketing 15 19 Domestic Finance 5 20 Domestic Marketing IS GMAT Score Industry experience 75 IS 562 85 S 553 10 90S 10 95 S 741 9 925 580 8715 507 2 775 7 805 720 973 666 855 611 90$ 691 905 528 865 73 723 965 627 775 655 10 833 885 670 895 590 915 589 700 651 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 D24 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 E24 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 G2 to return "low", "Average" or "High" based on the applicants' industry experience. You can find the criteria in the below table. (10 points) 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 N22 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) Experience Level School based Finacial aid Department based finaciat ad 75 85 90 95 92 87 77 Applicant la 1 2 3 4 5 6 7 B 9 10 11 12 13 14 15 16 17 18 19 20 Origin Domestic International Domestic Interational International International Interational International International International International Domestic Domestic Domestic Domestic Domestic International Domestic Domestic Domestic Department Information Systems Marketing Finance Marketing Finance Marketing Finance Marketing Finance Information Systems Finance Marketing Information Systems Marketing Finance Information Systems Finance Marketing Information Systems Marketing |||||||||||||=|a 97 85 90 GMAT score 567 553 645 741 680 507 619 720 666 611 691 628 700 723 627 655 631 670 590 589 Years of Industry experience 4 10 10 9 2 2 7 8 2 4 6 5 2 4 4 10 5 8 14 7 86 87 96 77 83 88 89 91 GPA Score Hlook 1 _School Based Finacial aid Look up 80 85 50% of tuition 80% of tuition 100% of tuition 90 pre f Finance finacial aid- Marketing finacial aid- Hlookup 2_Department Based Finacial aid 600 650 700 $1,000 $5,000 $10,000 $2,000 $4,000 $8,000 Domestic or International --> GPA Applicant. Origin Department Domestic Finance 2 internations Marketing 3 Domestic Finance 4 internations Marketing interation Finance 6 Internations Marketing internation Finance internations Marketing 9 international Finance 10 interations Marketing 11 international Finance 12 Domestic Marketing 13 Domestic Finance 14 Domestic Marketing 15 Domestic Finance 16 Domestic Marketing 17 international Finance 18 Domestic Marketing 19 Domestic Finance 20 Domestic Marketing 15 5 S S IS S 3 S 5 $ S s Is S IS S S S S 5 GMAT Score Industry experience 75 S 567 85 IS 553 10 90 S 645 10 955 741 9 92 IS 680 87 IS 607 7715 619 80$ 720 97 IS 666 2 85 IS 611 4 90 IS 691 6 90S 628 5 86 S 700 2 875 723 95 IS 627 4 77 S 655 10 83 S 531 88S 670 895 590 4 913 589 545 619 Applicant Origin Department GPA Domestic Finance $ 2 International Marketing IS Domestic Finance s 4 International Marketing 5 5 International Finance 15 6 International Marketing S 7 International Finance 15 International Marketing $ 9 International Finance 10 International Marketing S 11 International Finance $ 12 Domestic Marketing 15 13 Domestic Finance 5 14 Domestic Marketing 5 15 Domestic Finance 5 16 Domestic Marketing 15 17 International Finance 15 10 Domestic Marketing 15 19 Domestic Finance 5 20 Domestic Marketing IS GMAT Score Industry experience 75 IS 562 85 S 553 10 90S 10 95 S 741 9 925 580 8715 507 2 775 7 805 720 973 666 855 611 90$ 691 905 528 865 73 723 965 627 775 655 10 833 885 670 895 590 915 589 700 651