Exp19 Excel Ch07 Ml1 Admissions Instructions

Exp19 Excel Ch07 Ml1 Admissions Instructions

Work in the Admissions Office for a small regional university in Massachusetts. An assistant entered a list of college applicants for Fall 2021. Determine if students qualify for early admission or early rejection based on SAT and GPA. Calculate a total score based on SAT and GPA to determine regular admissions and rejections. Perform the following steps in Excel using a provided file:

Paper For Above instruction

Begin by opening the Excel file named Exp19_Excel_Ch07_ML1_Admissions.xlsx. The file contains data about college applicants, including their application received dates, SAT scores, GPA, and residency status. Your task is to analyze this data to determine admissions decisions based on specified criteria and to summarize key statistics.

First, calculate the number of days between the application’s initial deadline and the date received. Use the DAYS function in cell D11, referencing cells B8 (initial deadline) and C11 (date received). Use correct mixed and relative references. Copy this formula down from D11 to D67 to handle all applicants. Negative values will indicate late submissions.

Next, determine if a student qualifies for early admission. In cell G11, insert an IF function nested with AND. The student qualifies if their SAT score is at least 1400 and the GPA is at least 3.80, based on thresholds in cells B3 and B4. Use proper references, then copy the formula down to G67. Students meeting both criteria get a “Yes” in the Admit Early column; otherwise, “No”.

Similarly, evaluate early rejection criteria in cell H11, using an IF function nested with OR. Reject a student early if their SAT score is less than 800 or their GPA is below 1.80, referencing cells C3 and C4. Copy down through H67, marking “Yes” or “No”.

Calculate each applicant’s total admission score in column I (starting I11) by multiplying GPA (F11) by a multiplier in B7, then adding their SAT score (E11). Use appropriate references. This score determines if they meet the threshold for admission.

In cell J11, use the IFS function to assign a final decision: if Admit Early is “Yes”, display “Early Admission”; if Reject Early is “Yes”, display “Early Rejection”. If the score exceeds the threshold in B6, display “Admit”; otherwise, “Reject”. Copy this formula down through J67.

Summarize key data points in the Summary Data section: count the number of early admissions using COUNTIF in H3, referencing the range J11:J67 and the condition from E3. Copy to H4 for total admits. Calculate the average SAT score for early admissions in I3 with AVERAGEIF on range E11:E67 where decisions are “Early Admission,” using mixed references. Similarly, calculate average GPA in J3 for early admissions. Format I3 and J3 to zero decimal places.

Further, count applications meeting specific criteria: in H5, use COUNTIFS to count applicants with scores ≥ 3500 and decision “Early Admission.” In H6, count in-state applicants (residence B11:B67 with “In State”) with early admission decisions. Also, find the highest score (MAXIFS) in H7, and average score (AVERAGEIFS) in H8, both based on residency and decision criteria.

Calculate average SAT scores in I5 and I6 for applicants with scores ≥ 3500 and in-state residents, or with early admission decisions, respectively. Copy formulas accordingly.

Create a map chart to display admissions by state: select range A1:B5 on the Map worksheet. Insert a map chart and set the chart title to “Admissions by State”. In the format options, select “Only regions with data” for the map area. Move and resize the map chart to C1 with specified dimensions (3.12" height, 3.26" width). Place footer with your name on the left, sheet name in the center, and filename on the right on all sheets.

Finally, save and close the file.

References

  • Excel functions: DAYS, IF, AND, OR, IFS, COUNTIF, AVERAGEIF, COUNTIFS, MAXIFS, AVERAGEIFS, CUMIPMT, PPMT, IPMT, FV, DATE, YEAR, MONTH, DAY. (Microsoft, 2023)
  • Excel Tips & Tricks for Data Analysis. TechRepublic. (2023)
  • Advanced Excel Formulas and Functions. Excel Easy. (2023)
  • Excel Charting and Mapping Techniques. Microsoft Support. (2023)
  • Data Analysis and Visualization in Excel. Coursera. (2023)
  • Professional Data Management with Excel. Udemy. (2023)
  • Excel for Business Analytics. Lynda.com. (2023)
  • Excel Financial Functions: A Complete Guide. Investopedia. (2023)
  • Excel Tips for Working with Geographic Data. GISGeography. (2023)
  • Using Excel for Academic and Institutional Data Analysis. Journal of Data Science. (2023)

Related Assignments