CS&E 101 LAB 5: NESTED IF’S & LOOKUPS



CS&E 1111/1112 Lab 5: nested if’s Lab 5: In-Lab Assignment –To be completed during in-lab classThe following assignment must be completed before the end of the lab session. If you have not finished all of the tasks, be sure to save and submit what you have completed for partial credit. In-lab 5expands your use of relational operators, Boolean logical constructs, IFs and Nested IFs to help create spreadsheets that enable decision making.Part I -Faber CollegePlease read through all of the instructions– the lab will lead you through each of the specific criteria and questions, after explaining the set-up and the overall goal.Dean Wormer and the faculty at Faber College have thought long and hard about their admissions criteria. They have decided to forget about activities, athletics, relatives who attended the university, recommendations, and so on. Faber is going to try to upgrade their academic reputation by using admission criteria based solely on academics. This will include standardized test scores and class rank. A copy of the completed spreadsheet appears at the end of the lab.Applicants must submit five numbers:Math (SAT test score) – valid scores range from 200 to 800Verbal (SAT test score) – valid scores range from 200 to 800One Science SAT II score- valid scores range from 250 to 800Number of students in graduating class – any positive number is validRank in graduating class – valid from 1 to number of students in graduating classThe Admissions Committee has set the following criteria to determine whether a student will be rejected, accepted or waitlisted. The criteria are applied in order. Thus, if a candidate is rejected, he cannot be accepted even if he meets the “accept” criteria. The committee first determines if an applicant will be rejected, then whether he will be accepted and finally if he is neither accepted nor rejected he will be waitlisted. The criteria used are as follows:An applicant is rejected if either of these criteria is true:The student has submitted an invalid score or class rank. Both SAT (Math & Verbal) test scores are below 500.An applicant is accepted if all of these criteria are true:The two SAT (Math & Verbal) scores average above 550The SAT II Science score is above 550.The applicant is in the top fifth of the graduating class.The applicant has not been rejectedAn applicant is waitlisted if he has neither been rejected nor accepted.Solving the Problem:Copy the Excel spreadsheet file faber.xlsx from Carmen to your personal directory on the Z: drive. The data input section of the spreadsheet has been completed as well as the structure of the adjacent output area. A copy of the completed spreadsheet can be found on the last page of this lab. This should help you determine if you are on the right track. As always, you will be graded on the formulas.It is your job to determine which students are rejected, accepted or waitlisted. – The instructions that follow are one possible method of solving this problem. All of the formulas that you write will correspond to one student’s data (Blutarski in row 4). The formulas must be written so that they can be copied down the column.To help make these decisions, we have broken them down into several sub-components as represented by the different columns – invalid scores, top 20% etc.. Your job will be to write formulas to combine the criteria needed for these decisions. Evaluating “reject” criteria – start with each of the criteria listed Write a formula in cell H4, which can be copied down the column to determine if the test scores are invalid. Math SAT scores are not accepted if they are below 200 – as students are not considered eligible to apply. They are also not accepted over 800 as they are an invalid value. To test if the Math SAT score is below 200 use this expression C4<200To test if the Math SAT score is above 800 write the expression____________ Test that Verbal SAT scores are invalidVerbal SAT score is below 200 ______________________________________Verbal SAT score is above 800 ______________________________________Test that SAT II Science test scores are invalidSAT II Science score is below 250 ____________________________________SAT II Science score is above 800 ____________________________________Class rank is invalid - (hint: refer to the criteria that were previously specified to determine what an invalid class rank is. Make sure to write your formula so that is can be copied down the column.) __________________________________________________ Combine this information in order to determine if you should reject the student. To do this you need to determine if any of the above arguments are TRUE. “Any” usually means that if even one is TRUE, the entire statement is TRUE. Given this information, what function should we use to combine all these terms? Not sure? Go back to your pre-lab and look for a similar problem.Test to see if both the Verbal and Math SAT scores are below 500 based on the definition provided in the problem description. Write a formula in cell I4 and copy it down the column. To test this criteria use the same methodology used in step 1. First breakdown each component part into a simple expression; then combine them using a Boolean operator function.Test to see if the candidate is “accepted” based on the definition provided in the problem description. Write a formula in cell N4 and copy it down the column. To test this criteria use the same methodology used in step 1. First breakdown each component part into a simple expression then combine them using a Boolean Operator Function.Test to see if the average of the SAT score are above 550. Write a formula in cell J4 and copy it down the column.Test to see if the SAT II Science test has a score above 550. Write a formula in cell K4 and copy it down the column.Test to see if the applicant is in the top 20% of his/her class. Write a formula in cell L4 and copy it down the column.Evaluating “Waitlist” - Write a formula in cell N4 to determine whether to “waitlist” this candidate. A candidate is waitlisted if they have not been rejected and not been accepted.Note: There are several combinations of Boolean functions which can be successfully combined to make this equation work.Answer the following questions.-679451067435This yrs. GPA Average >3.1This yrs. GPA Average = 3.1higherTRUEFALSETRUEFALSEThis yrs. SATII Average >610This yrs. SATII Average = 610TRUEFALSETRUEFALSElowersame00This yrs. GPA Average >3.1This yrs. GPA Average = 3.1higherTRUEFALSETRUEFALSEThis yrs. SATII Average >610This yrs. SATII Average = 610TRUEFALSETRUEFALSElowersameThe dean has asked you to report to him how the applicant pool compares with last year’s applicant pool. To do this they have asked you to compare the average the SATII scores of this year’s applicants and compare to last years, which was 610. If the average of the SATII scores this year was higher, display “higher”, if the average of the SATII this year is the same as last year, display “same”, if it was lower, display “lower”. Write your formula in cell B19 as described here: Use an IF function to solve this using the following logic diagram The faculty is thinking of offering a scholarship to students with excellent SAT scores. If a student’s Math and Verbal SAT scores (combined) is above 1300 they will be considered a scholarship candidate. Write an Excel formula to determine if this student is a "scholarship candidate". If the student is not a candidate display "no scholarship". Place your formula in column P (not shown) and copy it down the column.Use Conditional Formatting to format the following:Use Data Bars to highlight the SATII scores in column E.Specify a yellow background and red bold font type for all cells listing as “scholarship candidate” in column P.Save and Submit this file Faber.xlsx under Lab 5 -20129536449000Completed Application Worksheet for Faber-CollegeSubmit your filesSubmit your lab files to the Lab 5 assignment for the pre-lab and in-lab assignments as follows:Soccer.xlsx (prelab)Movies.xlsx. (prelab)Faber.xlsx (inlab) Logoff OF your computer ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download