Office 2016 – myitlab:grader – Instructions Excel Project
EX16_XL_CH07_GRADER_ML1_HW - University Admissions
Office1.3
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 2018 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 andGPA to determine regular admissions and rejections.
Instructions:
For the purpose of grading the project you are required to perform the following tasks
Points
Step Instructions
Possible
Open exploring_e07_grader_h2_Admissions.xlsx and save it as
1 0.000
exploring_e07_grader_h2_Admissions_LastFirst.
In cell E14, enter a date function that calculates the number of days between the Initial Deadline (cell
E10) and the Date Received for the first student (D14). Use mixed and relative references appropriately.
2 Copy the function to the range E15:E68. A negative value indicates the application was received after 5.000
the initial deadline.
In cell H14, enter a nested logical function to display either Yes or No in the Admit Early column. The
university admits a student early if that student meets both the Early Admission criteria for the SAT (cell
B6) and GPA (cell B7). That is, the student’s SAT score must be 2000 or higher, and the GPA must be
3 3.80 or higher. Use relative and mixed references to the cells in the Admission Criteria range. Based on 10.000
the requirements, the first student, Frank Aaron, will not be admitted early.
In cell I14, enter a nested logical function to display either Yes or No in the Reject Early column. The
university rejects a student early if that student has either an SAT score less than 1000 (cell C6) or a
4 10.000
GPA below 1.80 (cell C7). Use relative and mixed references to the cells in the Admission Criteria
range.
In cell J14, enter a formula to calculate an applicant’s admission score. Apply the multiplier (found in
5 the Miscellaneous Standards & Filter range) to the student’s GPA and add that score to the SAT. Frank 10.000
Aaron’s score is 3496.
In cell K14, enter nested IF functions inside a main IF function. The final 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 10.000
6
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 a mixed reference to the cell in the Miscellaneous Standards & Filter
range.
Copy the formulas down the Admit Early, Reject Early, Score, and Final Decision columns. To paste the
7 formulas, click Paste on the Home tab and click Formulas to copy just the formulas. If you use a regular 6.000
Paste option, the double borders will copy also.
In cell H6, enter a database function to count the total number of early admissions with >=2900
8 threshold scores. Use the range L5:M6 for the criteria range and J13 for the field. 5.000
Current_Instruction.docx
EX16_XL_CH07_GRADER_ML1_HW - University Admissions
Office1.3
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 2018 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 andGPA to determine regular admissions and rejections.
Instructions:
For the purpose of grading the project you are required to perform the following tasks
Points
Step Instructions
Possible
Open exploring_e07_grader_h2_Admissions.xlsx and save it as
1 0.000
exploring_e07_grader_h2_Admissions_LastFirst.
In cell E14, enter a date function that calculates the number of days between the Initial Deadline (cell
E10) and the Date Received for the first student (D14). Use mixed and relative references appropriately.
2 Copy the function to the range E15:E68. A negative value indicates the application was received after 5.000
the initial deadline.
In cell H14, enter a nested logical function to display either Yes or No in the Admit Early column. The
university admits a student early if that student meets both the Early Admission criteria for the SAT (cell
B6) and GPA (cell B7). That is, the student’s SAT score must be 2000 or higher, and the GPA must be
3 3.80 or higher. Use relative and mixed references to the cells in the Admission Criteria range. Based on 10.000
the requirements, the first student, Frank Aaron, will not be admitted early.
In cell I14, enter a nested logical function to display either Yes or No in the Reject Early column. The
university rejects a student early if that student has either an SAT score less than 1000 (cell C6) or a
4 10.000
GPA below 1.80 (cell C7). Use relative and mixed references to the cells in the Admission Criteria
range.
In cell J14, enter a formula to calculate an applicant’s admission score. Apply the multiplier (found in
5 the Miscellaneous Standards & Filter range) to the student’s GPA and add that score to the SAT. Frank 10.000
Aaron’s score is 3496.
In cell K14, enter nested IF functions inside a main IF function. The final 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 10.000
6
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 a mixed reference to the cell in the Miscellaneous Standards & Filter
range.
Copy the formulas down the Admit Early, Reject Early, Score, and Final Decision columns. To paste the
7 formulas, click Paste on the Home tab and click Formulas to copy just the formulas. If you use a regular 6.000
Paste option, the double borders will copy also.
In cell H6, enter a database function to count the total number of early admissions with >=2900
8 threshold scores. Use the range L5:M6 for the criteria range and J13 for the field. 5.000
Current_Instruction.docx
