You work in the Admissions Office for West Coast University, a mid-sized regional university in…

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 2012 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. You need to rank each applicant and determine each applicant’s percent rank based on the calculated total score. Finally, you need to perform statistical calculations to determine averages and counts based on a combination of factors, such as in-state early admission.

a. Open e07m1admissions, and then save it as e07m1admissions_LastnameFirstname.

b. Enter a nested logical function in the Admit Early column 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, Frank Aaron will not be admitted early.

c. Enter a nested logical function in the Reject Early column 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. Frank Aaron will not be rejected early either. That is, the decision about Frank’s admission is still pending.

d. Enter a formula in the Score column to calculate an applicant’s admission score. Apply the multiplier (found in the Miscellaneous Standards & Filter range) to the student’s GPA, and then add that score to the SAT. Frank Aaron’s score is 3496.

e. Enter a nested IF function inside a main IF function in the Final Decision column. 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 cell in the Miscellaneous Standards & Filter range. Because Frank’s combined score of 3496 is greater than or equal to the 2900 threshold, the decision is Admit.

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

g. Use the “better practices” functions to calculate each student’s admission-score ranking and percent rank to identify what percentile each score is. Sort the list by admission score, displaying the highest score first. Because several students have identical top scores of 4700, no one has a rank of 1. The average top rank is 2.0. These students are at the 99.4% percentile.

h. Calculate the percent rank of the threshold score in cell B12. Scroll through the list to ensure no one was admitted below that percentile. This is a cross-check to ensure formulas are correct.

i. Enter summary statistics to calculate the counts and averages of early and regular admissions and rejections in the Summary Data range. Create the first count and two average functions to copy it down in such a way to enable the least amount of editing of the copied functions. Use mixed and absolute cell references correctly in the formulas.

j. Calculate the number of total applicants, total admissions, and acceptance rate. The acceptance rate is the total admissions percentage of the total applicants. Calculate the average SAT and GPA for the total applicants.

k. Enter database functions to calculate the average SAT and GPA for total admissions. Note that you cannot merely average the existing averages because the counts are different for Early Admits and Regular Admits. Use the Final Decision criteria range (C11:C13). You can use the database function without performing an advanced filter with an output range. The average GPA for total admissions is 3.55.

l. Calculate the average GPA for the state/admissions combination (such as In-State Early Admission) in the range J7:J10. Calculate the number of each combination in the range K7:K10. Apply borders again as needed after copying the formulas.

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

n. Save and close the workbook, and submit based on your instructor’s directions.