MIS 211 Midterm Exam

MIS 211 Midterm Exam
1.    Open the Excel file named MIS211 Midterm and rename it – Spring 2016 Exam.
2.    Go to the ‘Tables’ worksheet and create named ranges for the two tables.  Name them; Grades and Scholarship.
3.    Lookup the letter grade in the grades table and put the value in the column marked GPA grade.
4.    Rank the GPAs in the column marked GPA Rank.
5.    Rank the SAT verbal scores and the SAT Quant scores in their respective columns.
6.    Add up the SAT verbal and quant scores and put the result in the Combined SAT scores column.
7.    Rank the combined scores.
8.    In order to be a Top Candidate a student must have a GPA of 3.1 or better and have a combined SAT score of 1200 or better.  Also, he or she must have 2 or more references and speak a foreign language.  The result in the Top Candidate column is either the Boolean TRUE or FALSE.
9.    If a student is not a top candidate he or she is wait listed – either TRUE or FALSE.
10.    Look up the scholarship amount they are eligible for.  This is retrieved from the Scholarship table using the Combined SAT rank for the row and the GPA rank for the column.  A student must be ranked in the top 5 for both in order to qualify for money.  Some students will have a #ref error if they do not qualify.
11.    For extra credit – use a iferror() function for number 10 so that the #ref error is replace with the string ‘NA’.  You don’t have to do it, it’s extra credit.
12.    Calculate the mean and median GPA and combined SAT score.
13.    Use conditional formatting to make all scholarship amounts over $4000 bold and red.
14.    Save your file and put it in the drop box.
15.    Have a nice day.
Powered by