EX16_XL_CH07_GRADER_ML1_HW - University Admissions Office 1.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 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	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. Copy the function to the range E15:E68. A negative value indicates the application was received after the initial deadline.	5.000
3	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 students SAT score must be 2000 or higher, and the GPA must be 3.80 or higher. Use relative and mixed references to the cells in the Admission Criteria range. Based on the requirements, the first student, Frank Aaron, will not be admitted early.	10.000
4	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 GPA below 1.80 (cell C7). Use relative and mixed references to the cells in the Admission Criteria range.	10.000
5	In cell J14, enter a formula to calculate an applicants admission score. Apply the multiplier (found in the Miscellaneous Standards & Filter range) to the students GPA and add that score to the SAT. Frank Aarons score is 3496.	10.000
6	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 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.	10.000
7	Copy the formulas down the Admit Early, Reject Early, Score, and Final Decision columns. To paste the formulas, click Paste on the Home tab and click Formulas to copy just the formulas. If you use a regular Paste option, the double borders will copy also.	6.000
8	In cell H6, enter a database function to count the total number of early admissions with >=2900 threshold scores. Use the range L5:M6 for the criteria range and J13 for the field.	5.000
9	In cell I6, enter a database function to calculate the average SAT score for early admissions with the >= threshold. Use the range L5:M6 for the criteria range.	5.000
10	In cell J6, enter a database function to calculate the average GPA for early admissions with >= threshold score. Use the range L5:M6 for the criteria range.	5.000
11	In cell H7, enter a database function to count the total number of in-state early admissions. Use the range L9:M10 for the criteria range and C13 for the field.	5.000
12	In cell I7, enter a database function to calculate the average SAT for early in-state admissions. Use the range L9:M10 for the criteria range.	5.000
13	In cell J7, enter a database function to calculate the average GPA for all in-state early admissions. Use the range L9:M10 for the criteria range.	5.000
14	In cell H9, enter a database function to calculate the highest score for all in-state early admissions. Use the range L9:M10 for the criteria range.	5.000
15	In cell H10, enter an INDEX function with a nested MATCH function to identify the last name of the person who had the highest overall score for in-state early admission. The function should display Brasington.	10.000
16	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.	4.000
17	Save and close the workbook, and submit the file as directed.	0.000
	Total Points	100.000

West Coast University Admissions Office

Applications for Fall 2018 Admission

Admission Criteria

Summary Data

Criteria Range 1

Standard

Early Admit

Early Reject

=

Description

Counts

Average SAT

Average GPA

Score

Final Decision

SAT

2000

1000

>= 2900 and Early Admissions

>=2900

Early Admission

GPA

3.80

1.80

In State Early Admissions

Criteria Range 2

Miscellaneous Standards & Filter

Initial Deadline

Highest In-State Score

Residence

Final Decision

Threshold Score

2900

3/1/2018

Person w/ Highest In-State Score

In State

Early Admission

Multiplier

600

Last

First

Residence

Date Received

Days Before Deadline

SAT

GPA

Admit Early

Reject Early

Score

Final Decision

Aaron

Frank

In State

2/14/2018

1306

3.65

Abrell

Sharon

Out State

12/29/2017

2350

3.00

Adili

Diane

In State

3/14/2018

1000

2.29

Alevy

Julie

Out State

11/15/2017

2300

4.00

Algeo

George

In State

2/15/2018

750

2.60

Altman

Samuel

Out State

3/1/2018

1150

2.29

Ancona

Jacqueline

Out State

2/28/2018

1200

3.35

Anderson

Leigh

In State

1/15/2018

1000

2.84

Andrade

Ellen

Out State

12/15/2017

1134

2.25

Applegate

Jennifer

In State

12/14/2017

2000

3.83

Armstrong

Elizabeth

In State

2/14/2018

2200

3.50

Arnold

Monica

Out State

2/1/2018

1000

2.35

Augustine

Bill

Out State

12/1/2017

1200

2.93

Austin

Alissa

Out State

11/30/2017

1100

4.00

Axelrod

Brian

In State

2/24/2018

2001

3.85

Bagley

Ken

Out State

2/2/2018

998

3.08

Bak

Read

Out State

1/15/2018

1574

3.18

Balmer

Hannah

In State

11/25/2017

1351

3.38

Barker

Shelly

Out State

12/15/2017

950

2.87

Barnard

Jim

In State

3/2/2018

1125

3.14

Barnes

Eric

In State

2/15/2018

950

2.10

Barr

Ann

In State

3/1/2018

1675

3.80

Barrera

Wendy

In State

2/28/2018

1600

3.55

Bartel

Edward

Out State

1/15/2018

1500

3.21

Basile

Jodi

In State

12/1/2017

975

2.67

Bath

LeDawn

In State

11/30/2017

945

2.22

Behrendt

Mary

Out State

2/24/2018

1685

3.73

Bell

Carmen

Out State

2/2/2018

900

3.24

Bergman

Cara

In State

11/25/2017

1225

3.38

Berlin

Dennis

Out State

12/15/2017

1098

2.45

Berman

Michael

Out State

1/14/2018

1795

3.65

Bermudez

Jennifer

Out State

3/4/2018

1374

4.00

Bersagel

Amy

In State

11/15/2017

884

3.60

Beville

David

Out State

2/15/2018

1800

3.10

Beville

Amy

In State

3/1/2018

1597

3.57

Billingslea

McClendon

In State

2/28/2018

945

2.99

Blackburn

Karen

In State

1/15/2018

2000

3.40

Blasco-Paul

Kristine

Out State

2/1/2018

1100

2.71

Block

Dainey

In State

12/1/2017

983

3.16

Blum

Maat

In State

11/30/2017

1016

3.34

Borden

Delia

In State

2/24/2018

931

3.02

Bove

Shawna

Out State

2/2/2018

800

2.98

Brasington

Diane

In State

1/15/2018

2124

3.98

Bratcher

Nugent

Out State

11/25/2017

1994

3.74

Braun

John

In State

12/15/2017

1750

3.75

Bredbenner

Catherine

Out State

2/1/2018

899

3.48

Brice

Gwyn

Out State

2/1/2018

1580

3.84

Bronson

Aleksandra

In State

1/15/2018

1132

3.55

Bruce

Frank

In State

12/1/2017

1206

3.51

Bruner

Robert

In State

11/30/2017

1552

2.16

Buchanan

Ronald

Out State

2/24/2018

1045

2.95

Buckley

Derek

Out State

2/2/2018

1376

2.06

Burgmeier

Devorah

Out State

11/25/2017

1200

2.13

Burgmeier

David

In State

12/15/2017

1884

3.79

Burrows

Mark

In State

1/14/2018

1574

3.68