You work in the Admissions Office for a small regional university in Massachusetts Your assistant entered a
Question:
You work in the Admissions Office for a small regional university in Massachusetts Your assistant entered a list of college applicants for the Fall 2021 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.
1 | Start Excel. Download and open the file named Exp19_Excel_Ch07_ML1_Admissions.xlsx. Grader has automatically added your last name to the beginning of the filename. |
2 | First, you want to calculate the number of days between the Initial Deadline and the Date Received. |
3 | Copy the DAYS function from cell D11 to the range D12:D67. A negative value indicates the application was received after the initial deadline. |
4 | Next, you want to determine if a student should be admitted early. |
5 | Now that you determined which students are admitted early, you want to determine if a student should be rejected early. |
6 | Use column I to calculate an applicant's admission score. |
7 | In column J, you want to display Early Admission, Early Rejection, Admit, or Reject, respectively, to indicate the final decision. |
8 | You are ready to copy the formulas down their respective columns. |
9 | You want to enter formulas in the Summary Data section to summarize key points. First, you want to determine the number of Early Admissions and the number of Admits. |
10 | Next, you want to calculate the average SAT score for Early Admissions. |
11 | You want to calculate the average GPA score for Early Admissions. |
12 | Apply Number format with zero decimal places to cell I3. Then, copy the functions in the range I3:J3 to the range I4:J4. |
13 | You want to count the number of applications that meet two conditions. |
14 | You want to count the number of applications that meet two more conditions. |
15 | You want to identify the highest score based on two conditions. |
16 | In cell H8, insert the AVERAGEIFS function to calculate the average score in the range I11:I67 that meets two conditions: Residences in the range B11:B67 are In State and Final Decisions in the range J11:J67 are *Adm*. Use the asterisks as wildcards so that it includes both Early Admission and Admit. |
17 | In cell I5, insert the AVERAGEIFS function to calculate the average SAT score that meets two conditions: Scores in the range I11:I67 are greater than or equal to 3500 and Final Decisions in the range J11:J67 are Early Admissions (cell E3). Use mixed references appropriately. |
18 | In cell I6, insert the AVERAGEIFS function to calculate the average SAT score that meets two conditions: Residences in the range B11:B67 are In State and Final Decisions in the range J11:J67 are Early Admissions (cell E3). Use mixed references appropriately. |
19 | Copy the functions from the range I5:I6 to the range J5:J6. |
20 | You want to insert a map that depicts admissions by state. |
21 | You want to place the top-left corner of the map in cell C1 and change the map size. |
22 | Create a footer with your name on the left side, the sheet name code in the center, and the file name code on the right side on all sheets. |
Introduction to Operations Research
ISBN: 978-1259162985
10th edition
Authors: Frederick S. Hillier, Gerald J. Lieberman