EXP ECH07 H2 West Coast University Admissions Office 1.2 complete solutions correct answers key

EXP ECH07 H2 West Coast University Admissions Office 1.2 complete solutions correct answers key

Project Description:

You work in the Admissions Office for West Coast University, a mid-sized regional university in California. Your assistant entered a list of college applicants for the Fall 2015 semester. You determine if a student qualifies for early admission or early rejection based on SAT and GPA. After determining the immediate admissions and rejections, you calculate a total score based on SAT and GPA to determine regular admissions and rejections.

Instructions:

For the purpose of grading the project you are required to perform the following tasks:

Step

Instructions

Points Possible

1

Open exploring_e07_grader_h2_Admissions.xlsx and save it as exploring_e07_grader_h2_Admissions_LastFirst.

0.000

2

Enter a nested logical function in the Admit Early column (in F17) to display either Yes or No. The university admits a student early if that student meets both the early admission criteria for the SAT and GPA. That is, the student’s SAT score must be 2000 or higher, and the GPA must be 3.80 or higher. Use appropriate references to the cells in the Admission Criteria range. Based on the requirements, the first student, Julie Alevy, will be admitted early.

10.000

3

Enter a nested logical function in the Reject Early column (in G17) to display either Yes or No. The university rejects a student early if that student has either an SAT score less than 1000 or a GPA below 1.80. Use appropriate references to the cells in the Admission Criteria range.

10.000

4

Enter a formula in the Score column (in H17) to calculate an applicant’s admission score. Apply the multiplier (found in the Miscellaneous Standards & Filter range) to the student’s GPA and add that score to the SAT. Julie Alevy’s score is 4700.

10.000

5

Enter a nested IF function inside a main IF function in the Final Decision column (in I17). The decision text should be one of the following: Early Admission, Early Rejection, Admit, or Reject. Hint: Two logical tests are based on the Yes/No displayed in the Admit Early and Reject Early columns. For regular admission, a student must have a combined admission score that is 2900 or higher. A student is rejected if his or her score is lower than the threshold. Use appropriate references to the cells in the Miscellaneous Standards & Filter range.

10.000

6

Copy the formulas down the Admit Early, Reject Early, Score, and Final Decision columns.

12.000

7

In cell F7, enter a database function to count the total number of early admissions with >= threshold scores. Use the criteria in cells K6:L7 in your function.

10.000

8

In cell F8, enter a database function to count the total number of in-state early admissions. Use cell references from the Criteria range in your function.

10.000

9

Enter database functions to calculate the average SAT (cell G7)  and GPA (cell H7) for all early admits with admissions scores at or above the threshold. Note that you cannot merely average the existing averages because the counts are different for Early Admits and Regular Admits. You can use the database function without performing an advanced filter with an output range (use cell references from the Criteria range in your function).

12.000

10

In cell H8, calculate the average GPA for all in-state early admits (use cell references from the Criteria range in your function).

10.000

11

Create a footer with your name on the left side, Page 1 of ? codes in the middle, and the file name code on the right side.

6.000

12

Save and close the workbook, and submit the file as directed.

0.000

Total Points

100.000