QUESTION
Write a formula in cell J4 that can be copied down the column to determine if (TRUE or FALSE) the applicant should be automatically disqualified based on the given criteria.
Exercise 4-1: Level 1 - Evaluating Job Applications for the BBB Company You work in the Human Resources Department (or simply HR) for the accounting firm BBB. The firm has recently increased its client base and hired several university graduates for entry-level positions. The HR manager has established a formal process for evaluating job applicants. This process takes into account the applicant's academic performance and work experience, as well as the impression made during the personal interview. In addition, all applicants are given a skills-based exam to determine their proficiency in spreadsheet and database applications. Because some applicants are not business majors but might be otherwise qualified for a position, the exam also covers some basic business concepts in accounting, finance, and marketing. You have been asked to evaluate the information on the candidates being considered. Each job application provides the following information: College GPA (valid scores range from 1.5 to 4.0) Major Code, universal standardized system that indicates the applicant's undergraduate major; for example, Engineering=1, Business=2, Economics=3, Physical Science=4, and so on (valid codes for majors are 1 through 100) The total number of references submitted by the applicant A Personal Interview Rating If the applicant has previous work experience (TRUE or FALSE) The Employment Exam score (valid scores are between 300 and 800) The undergraduate school ranking (compared with all colleges across the country) The HR manager has established criteria to determine if an applicant will be automatically disqualified or automatically hired, or if no decision is made. The criteria, which are applied in order, are described in the following list. An applicant is automatically disqualified if any of the following criteria are TRUE: The applicant has submitted an invalid GPA score, Employment Exam score, or Major Code. The applicant has a GPA less than 3.0. The applicant provided fewer than two references. The applicant has an Employment Exam score below 600. The applicant has a Personal Interview Rating of less than 3. An applicant is automatically hired if all of the following criteria are TRUE: The applicant has not been automatically disqualified. The applicant has a GPA score over 3.8. The applicant has a Major Code between 1 and 25 (inclusive). The applicant graduated from one of the top 50 schools (ranking of 50 or less). The applicant has an Employment Exam score above 720. The applicant has a personal interview rating of 4 or higher. The applicant has prior work experience. If an applicant is neither automatically disqualified nor automatically hired, the applicant's status is undecided. AutoSave OFF 4-Hiring-BBB Home Insert Draw Page Layout Formulas Data Review View Tell me Calibri (Body) V 11 AF A ab General Insert D Delete Paste BI U y V V A v $ % ) % 989 Conditional Format Cell Formatting as Table Styles Format Sort & Filter J4 * for 3 A B D E F H J College GPA Name Major Code Refer- ences Personal Interview Rating G Employ- ment Exam Score Work Experience School Rank Invalid Scores/ Major? Automatically Disqualified 3 4 Anderson 3.7 2 2. 5 TRUE 650 12 TRUE 3 5 Carolyn 3.8 16 5 6 FALSE 720 8 TRUE 6 Greg 2.0 15 2 3 3 TRUE 780 5 TRUE 7 Jackie 3.2 30 N 4 TRUE 710 3 TRUE 8 Lindsey 3.9 4 3 4 TRUE 740 15 TRUE 9 Max 3.8 19 4 1 TRUE 760 6 TRUE 10 Ryan 4.0 18 3 5 TRUE 800 1 TRUE 11 Sandra 3.0 20 1 5 TRUE 600 26 TRUE 12 Steven 2.6 500 2 3 FALSE 299 55 FALSE 13 Exercise 4-1: Level 1 - Evaluating Job Applications for the BBB Company You work in the Human Resources Department (or simply HR) for the accounting firm BBB. The firm has recently increased its client base and hired several university graduates for entry-level positions. The HR manager has established a formal process for evaluating job applicants. This process takes into account the applicant's academic performance and work experience, as well as the impression made during the personal interview. In addition, all applicants are given a skills-based exam to determine their proficiency in spreadsheet and database applications. Because some applicants are not business majors but might be otherwise qualified for a position, the exam also covers some basic business concepts in accounting, finance, and marketing. You have been asked to evaluate the information on the candidates being considered. Each job application provides the following information: College GPA (valid scores range from 1.5 to 4.0) Major Code, universal standardized system that indicates the applicant's undergraduate major; for example, Engineering=1, Business=2, Economics=3, Physical Science=4, and so on (valid codes for majors are 1 through 100) The total number of references submitted by the applicant A Personal Interview Rating If the applicant has previous work experience (TRUE or FALSE) The Employment Exam score (valid scores are between 300 and 800) The undergraduate school ranking (compared with all colleges across the country) The HR manager has established criteria to determine if an applicant will be automatically disqualified or automatically hired, or if no decision is made. The criteria, which are applied in order, are described in the following list. An applicant is automatically disqualified if any of the following criteria are TRUE: The applicant has submitted an invalid GPA score, Employment Exam score, or Major Code. The applicant has a GPA less than 3.0. The applicant provided fewer than two references. The applicant has an Employment Exam score below 600. The applicant has a Personal Interview Rating of less than 3. An applicant is automatically hired if all of the following criteria are TRUE: The applicant has not been automatically disqualified. The applicant has a GPA score over 3.8. The applicant has a Major Code between 1 and 25 (inclusive). The applicant graduated from one of the top 50 schools (ranking of 50 or less). The applicant has an Employment Exam score above 720. The applicant has a personal interview rating of 4 or higher. The applicant has prior work experience. If an applicant is neither automatically disqualified nor automatically hired, the applicant's status is undecided. AutoSave OFF 4-Hiring-BBB Home Insert Draw Page Layout Formulas Data Review View Tell me Calibri (Body) V 11 AF A ab General Insert D Delete Paste BI U y V V A v $ % ) % 989 Conditional Format Cell Formatting as Table Styles Format Sort & Filter J4 * for 3 A B D E F H J College GPA Name Major Code Refer- ences Personal Interview Rating G Employ- ment Exam Score Work Experience School Rank Invalid Scores/ Major? Automatically Disqualified 3 4 Anderson 3.7 2 2. 5 TRUE 650 12 TRUE 3 5 Carolyn 3.8 16 5 6 FALSE 720 8 TRUE 6 Greg 2.0 15 2 3 3 TRUE 780 5 TRUE 7 Jackie 3.2 30 N 4 TRUE 710 3 TRUE 8 Lindsey 3.9 4 3 4 TRUE 740 15 TRUE 9 Max 3.8 19 4 1 TRUE 760 6 TRUE 10 Ryan 4.0 18 3 5 TRUE 800 1 TRUE 11 Sandra 3.0 20 1 5 TRUE 600 26 TRUE 12 Steven 2.6 500 2 3 FALSE 299 55 FALSE 13
