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 Less than 3 years greater than or equal 3 and less than 5 years Greater than or equal 5 years Experience Level LOW Average 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 Le GPA 75 85 90 95 92 GMAT Score 567 553 645 741 Years of Industry experience 4 10 10 9 87 77 80 1 Applicant id 2 1 3 2 4 3 5 4 6 5 7 6 8 7 9 8 9 11 10 12 11 12 14 13 15 14 16 15 17 16 18 17 19 18 20 19 21 20 29 10 Origin Domestic International Domestic International International International International International International International International Domestic Domestic Domestic Domestic Domestic International Domestic Domestic Domestic 680 607 619 720 666 611 97 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 2 2 7 8 2 4 6 85 90 691 13 90 5 86 87 96 77 628 700 723 627 655 2 4 4 10 5 83 88 631 670 590 8 89 91 4 7 589 E G H F Years of Industry experience 4 GMAT score 567 Experience Level School based Finacial aid Department based Finacial aid 553 10 645 741 680 10 9 2 2 7 8 2 4 607 619 720 666 611 691 628 700 723 627 655 631 670 590 589 6 5 2 4 4 10 5 8 4 7 90 GPA Score Hlook 1 School Based Finacial aid Look up 80 85 50% of tuition 80% of tuition 100% of tuition GMAT Score Department of Finance finacial aid Department of 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 --> 6 7 Applicant Origin Department GPA GMAT score Industry experience 8 1 Domestic Finance $ 75 $ 567 4 9 2 Internationa Marketing $ 85$ 553 10 10 3 Domestic Finance $ 90 $ 645 10 11 4 Internation Marketing $ 95$ 741 9 12 5 Internation Finance $ 92|$ 680 2 13 6 Internationa Marketing $ 87 $ 607 2 14 7 Internation Finance $ 77 S 619 7 15 8 Internation Marketing $ 80S 720 8 16 9 Internationa Finance $ 97 $ 666 2 17 10 Internationa Marketing $ 85 $ 611 4 18 11 Internation Finance $ 90$ 691 6 19 12 Domestic Marketing $ 90 $ 628 5 20 13 Domestic Finance $ 86 $ 700 2 21 14 Domestic Marketing $ 87 S 723 4 22 15 Domestic Finance $ 96 $ 627 23 16 Domestic Marketing $ 77 S 655 10 24 17 Internation Finance S 83 | $ 631 5 25 18 Domestic Marketing $ 88$ 670 8 26 19 Domestic Finance $ 89S 590 4 27 20 Domestic Marketing $ 91 $ 589 28 29 30 Question 1-5 Question 6-7 Question 8 Ready