Web.cse.ohio-state.edu



CSE4194 Lab3DATE GIVEN: Wednesday, January 28, 2015DATE DUE: Friday, January 30, 2015 9:00amSUBMIT:to Dropbox Lab 3 folder on CarmenOBJECTIVESNamed cell/range PI vs PI()Conditional formattingMathematical functions: COUNT, COUNTIF, COUNTIFS, plus same type of SUM/AVERAGE functionsLogical functions: AND, OR, NOT, IF functionsOther functions: LARGE, SMALL, RANK functionsDIRECTIONSDownload the Lab3 Excel file from the instructor’s announcement page.On the “students” worksheet, where the passing score is defined in cell B1:Write an Excel formula in cell D4 (being sure to put a descriptive column header for each new set of data going forward) , which can be copied down to cell D11, to determine true or false, if Student A does *not* have a passing score (based on the score given in cell C1 as being the lowest possible passing score).Write an Excel formula in cell E4, which can be copied down to cell E11, to determine true or false, if Student A *did* pass.Write an Excel formula in cell F4, which can be copied down to cell F11, to determine true or false, if Student A *did* pass… using a different solution than the above question.NOTE: For questions 3 and 4, be sure not to use an IF function (because it’s not needed)Write an Excel formula in cell G4, which can be copied down to cell G11, to determine “yes” or “no”, if Student A did pass.Write an Excel formula in cell H4, which can be copied down to cell H11, to determine “yes” or “no”, if Student A did NOT pass.*** Put the value 75 in cell D1Write an Excel formula in cell I2, which can be copied down to cell I11, to determine if Student A has a “High” pass, “Medium” pass or “No” pass where:a “High” pass is a score greater than 75a “No” pass is a score less than 60otherwise, the score is a “Medium” scoreTechnically, there are 6 different combinations in which the above formula can be written. Write two more formulas, one each in columns J and K, making sure the second argument is different each time. NOTE: This means that for Q7 and the two Q8 functions, the first condition should result in a “High” value as the second argument, another first condition should result in a “Medium” value as the second argument, and another first condition should result in a “No” value as the second argument. Write an Excel formula in cell L2, which can be copied down to cell L11, to determine the grade for each student where an “A” is a score greater than or equal to 90, “B” is a score greater than or equal to 80 but less than 90, “C” is a score greater than or equal to 70 but less than 80, “D” is a score greater than or equal to 60 but less than 70, and “E” is a score less than 60.Write an Excel formula to determine, true or false, if all of the students scored at least 60 points:In cell E14, using only mathematical functions (see objectives section above)In cell E15, using only logical functions (see objectives section above)Write an Excel formula to determine, true or false, if any of the students scored less than 60 points:In cell E17, using only mathematical functions (see objectives section above)In cell E18, using only logical functions (see objectives section above)In cell row 19, explain what this formula is determining: =NOT(OR(E4:E11)). Use some cell merging and text wrapping to make it easier to read your answer.In cell row 20, explain what this formula is determining: =NOT(OR(D4:D11)). Use some cell merging and text wrapping to make it easier to read your answer.Write an Excel formula in cell D22, which can be copied down to cell D23, to determine the average score for all the male students using only mathematical functions (see objectives section above).NOTE: as you copy down one row, you should be determining the average score for all the female students.Class correction Add an “s” to your function name. What error occurred? Explain what this error means in cell F22.On the “subject” worksheet:Write an Excel formula in cell E2, which can be copied down and across to cell G9, to determine, true or false, if student A likes math i.e. the value in cell E1 compared to the student’s subject preference in cell D2. NOTE: When you check the results (which you should always do), you should find that the value in cell E2 is incorrect. If all the other results are correct, then you can assume that technically, the result in cell E2 is correct… but there is a problem that you need to find.Write an Excel formula in cell H2, which can be copied down to cell H9, to determine the rank using the score value for Student A. NOTE: A rank of 1 should denote the highest score.Write an Excel formula in cell I2, which can be copied down to cell I9, to determine the new grade for Student A based on the following: the top half of the class receives a grade of “X” and the bottom half of the class receives a grade of “Y”. If there is an odd number of students in the class, give the benefit to the higher grade i.e. let there be more X’s than Y’s.On the “try_it” worksheetWhy is AU13 not the best worksheet name? HINT: In cell A1 type =AU13.In cell A2, type: =PI() and in cell B2, type: =PI. What error did you get when you tried to use the PI function without the parentheses? Explain what this error means in cell C2.Find the Name Box on the worksheet (see picture given below). As you click in one cell after another in the worksheet, you will notice that the Name Box specifies the name of each cell. Now click on cell C3. In the Name Box, type PassScore (remembering that Excel is case IN-sensitive) and hit Enter. Notice that this name is showing in the Name Box. Click on cell E3 and back on cell C3. The Name Box should still have the new name for the cell that you typed in, PassScore.Write an Excel formula in Cell D6, which can be copied down to cell D13, to determine, true or false, if Student A passed the class, using the PassScore name reference instead of cell C3. NOTE: This type of named cell is an absolute reference. That is, using PassScore is the same as using $C$3. Check the cell references for all of the cells you just filled in by copying the formula down. In this case, it is not an issue to have the extra $ sign in front of the column designation in cell C3 since we are only copying in one direction. Because cell references can’t always work as absolute references in both column and row directions, it is not always possible to name a cell and use it in a formula. I want to easily see the students who do not have a passing score. Highlight the range D6:D13Choose conditional formatting on the home ribbonAt the bottom of the drop down menu, there's an option "manage rules"Click on the "new rule" button (could have picked this from drop down menu as well)Choose "use a formula to determine which cells to format" (last option)In the box below where it says "format values where this formula is true:" type =D6=false Click "format" button and choose the bold italics option and OKClick OK again (on the New Formatting Rule window)Under the "apply to" column, make sure you have: =$D$6:$D$13; you highlighted this range before you started so should already be set; but if you forget to highlight the range to be conditionally formatted, you can specify it here.Click either APPLY or OKChange cell C13 to a score of 50. You should have noticed that the values along with the conditional formatting in column D also updated. ................
................

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

Google Online Preview   Download