SunView is a small College in Arizona. They raised 2.8 million dollars from donors and are using that money to fund undergraduate student scholarships. Specifically, they are funding sports scholarships and a new scholarship for the best students enrolling in their new business analytics major.

You're helping SunView College better understand their student base, how many of their students qualify for different types of scholarships, etc. In order to assist with their business understanding efforts, you will create an Excel spreadsheet that considers the rules for different scholarships and answers a number of questions relevant to SunView College. The first task is to create a spreadsheet that assesses the qualifications of twelve students. For each one of these students you have access to the following information:

• How often the student practices sports (weekly)
• The student's overall grade (0-100%)
• Whether the student is currently considering the analytics major (Yes/No)

SunView College provided you with the following rules:

• Is he/she an 'A' student? (TRUE/FALSE): overall grade equals to or greater than 94%;
• Qualify for sports scholarship? (YES/NO): practice sports at least three times per week and overall grade is at least 88%;
• Qualify for Analytics scholarship? (YES/NO): The student is currently considering the Analytics major (first condition). In addition, he/she practices sports at least 4 times per week or is an 'A' student (second condition).

Based on these rules, and using the Excel template available here Download Excel template available here, you need to complete the following tasks:

TASK 1: Update the Excel template to identify the "A" students (TRUE/FALSE) in column E. To address this task, use cell references and a function to automate the process (i.e, you should not type "True" or "False" in the relevant cells);

TASK 2: Update the template to identify which students qualify for a sports scholarship (YES/NO) in column F. To complete this task, you should use an IF function;

TASK 3: Update the template to identify which students qualify for the Analytics scholarship (YES/NO) in column G. To complete this task, you should use an IF function;

TASK 4: Update the template to enter the students' letter grades in column H. To complete this task, you should use a LOOKUP function that uses the information available in cell range G25:H31;

TASK 5: Update the template by answering the questions in cells A24:A27. Your answers should be provided in cells B24:B27. To answer these questions you should use COUNTIF and SUMIF functions.

Excel sheet:

Student How Often Practice Sports WK? Overall Grade Considering Analytics Major Is He/She an 'A' Student? Qualify for Sports Scholarship? Qualify for Analytics Scholarship? Letter Grade Cooper Anderson 3 77% NO Alex Eldredge 4 88% YES Rachel Hale 2 94% YES Kellen Law 2 81% NO Mekena MacPherson 2 80% NO Wayne Montgomery 1 87% YES Cristina Motruna 3 88% YES Jon Parker 3 96% NO David Reiss 2 83% NO Thomas Riding 1 79% NO Nick Vasquez 1 83% YES Tony Vivirito 3 84% NO RULES Is He/She an 'A' Student? (TRUE/FALSE) Overal grade equals to or greater than 94% Qualify for Sports Scholarship? (YES/NO) Practice sports at least three times per week and overall grade is at least 88% Qualify for Analytics Scholarship? (YES/NO) Considering Analytics major (first condition). In addition, practice sports at least 4 times per week or is an 'A' student (second condition) Number of 'A' Students: Grade Range (=>) Letter Grade Number that Qualify for Sports Scholarship 74 C Number that Qualify for Analytics Scholarship 77 C+ Total # Days 'A' Students Practice Sports 80 B- 84 B 87 B+