CSE200 SP07 KREEVES QUIZ#1 SEAT# ___________

CSE200 WI10 KREEVES QUIZ#2 SEAT# ____________

NAME _______________________________________________ Lecture: TR 1:30-3:18pm

Lab section (check one): _______ F 1:30-3:18pm ________ F 3:30-5:18pm

Instructions:

· Filling out the correct seat# and lab section on both the test and the answer sheet is worth 2 points.

· Put away all books, papers, calculators, cell phones and music devices.

· Turn off all beepers, cell phones and anything else that you can turn off that might make noise.

· Read each question carefully and fill in the answer in the space provided. Answers must be legible or they will be marked incorrect. If there are multiple answers to choose from, please CIRCLE the correct answer. The question will not be graded at all if there are multiple answers to choose from.

· Please stay in the answer box for each question i.e. do not write an essay for a short answer problem!

· When time has run out you will be told to put all pens/pencils down.

· Be sure to use values as determined by previous problems and do not use values from problems that have not yet been solved per the ordering of the questions.

· Use cell references whenever possible and appropriate.

· Do NOT put “quotes” around Boolean (i.e. true/false) values.

· Don’t use a $ if NOT copying.

· Only use the functions given.

· Do not use unnecessary functions, for example =average(sum(a1:a5)). If you use an extra function that is not needed (the “average” function in the given example), it’s assumed that you thought it was necessary thus will be marked incorrect.

· Your answer should update correctly when additional input data is added to the problem or when input data is changed – unless otherwise noted.

WORKSHEET DESCRIPTIONS

Q2runs – The Excel-lent Ski Lodge and Resort has a large skiing area consisting of many different hills/slopes, also called runs, for skiers. Currently, each skier is identified on the Q2runs worksheet with the number of hills they ski down in column B called the number of runs, the time they are actually skiing down the slopes separated by hours and minutes, their gender, and experience (none, beginner, intermediate, advanced or expert), respectively. This is the input data given in the range A5:F16.

Q2unit – This worksheet has constant information as well as conversion factors needed to calculate new data. Be sure to use the data given here as a cell reference (not a constant value). I understand that most of these values don’t actually change, (except very possibly the value in B2) however this is being used as a learning experience so please be sure to “use cell references whenever possible”.

NOTICE: The functions you can use for this exam are listed on the worksheet page i.e. You can only use the AND, OR, and NOT functions on this exam.

CSE200 WI10 KREEVES ANSWER SHEET QUIZ#2 SEAT# ____________

NAME _______________________________________________ Lecture: TR 1:30-3:18pm

Lab section (check one): _______ F 1:30-3:18pm ________ F 3:30-5:18pm

Q#	PTS	MINUS	QUESTION and ANSWER
1	4		Write an Excel formula in cell Q2runs!G5, which can be copied down and across to cell Q2runs!J16, which determines if (i.e. TRUE or FALSE) jack is a beginner skier. =$F5=G$4 No extra $ allowed
2	5		Write an Excel formula in cell Q2runs!G17, which can be copied across to cell Q2runs!J17, which determines if (i.e. TRUE or FALSE) none of the skiers are beginner skiers. NOTE: The range G18:J18 is just here to help you with the labels. =NOT(OR(G5:G16)) Optional $ on row
3	4		Of the following formulas, determine which ones will correctly find the solution to the following: Write an Excel formula (cell not shown) to determine if there is at least one person in every level (beginner, intermediate, advanced, expert). a. =NOT(AND(G17:J17)) Correct? Yes _______ No ________ b. =NOT(OR(G17:J17)) Correct? Yes _______ No ________ c. =AND(G17:J17) Correct? Yes _______ No ________ d. =AND(NOT(G17),NOT(H17),NOT(I17),NOT(J17)) Correct? Yes _______ No ________
4	5		Write an Excel formula in cell Q2runs!K5, which can be copied down to cell Q2runs!K16, which determines if (i.e. TRUE or FALSE) jack is an inexperienced skier. An inexperienced skier is someone who either is a beginner or has no experience at all. =OR(F5="none",F5="beginner") Can say G5 or G5=TRUE instead of F5=”beginner” =and(f5<>"expert",f5<>"advanced",f5<>"intermediate") =NOT(OR(H5:J5)) Optional $ on column no quotes around TRUE/FALSE values allowed
5	7		Write an Excel formula (not shown) to determine if ONLY males are expert skiers. NOTE: Due to the type of problem this is, the values in the gender column cannot change, but everything else can change and your answer should still work. =AND(OR(J5,J7:J8,J10:J15),NOT(OR(J6,J9,J16))) okay if NOT/OR is J6=FALSE,J9=FALSE,J16=FALSE also NOT instead of FALSE okay if have J5=TRUE but can’t have J10:J15=TRUE no quotes around TRUE/FALSE values allowed
6	6		Write an Excel formula in cell Q2runs!L5, which can be copied down to cell Q2runs!L16, to determine the number of miles jack skied based on the number of runs he skied and the average length of each run (see the Q2unit worksheet). NOTE: Be sure to use cell references whenever possible. *=Q2unit!B$2Q2unit!B$4/Q2unit!B$3B5* Optional $ on column Order can be different but correct operator must go in front of designated cell reference
7	6		a. What type of chart is shown on the worksheet page? LINE b. Give the data range used for the creating the chart. Be specific! No $ signs are necessary though. =Q2runs!$A$4:$A$16,Q2runs!$L$4:$L$16 Okay if no $ and don’t need worksheet name either but do need both ranges, separately. c. Name two other different types of charts, besides the one mentioned in part a above, as learned in class. Can say two of the following: PIE, XY/Scatter, or BAR/COLUMN Cannot give bar and column as two answers or XY and scatter as two answers. These are the same kind of charts, just different formatting
8	7		Write an Excel formula in cell Q2runs!M5, which can be copied down and across to cell Q2runs!O16, to determine the calories that jack will burn based on an intensity level of burning 400 calories/hour (in cell Q2runs!M4). HINT: You will need the time that jack spent on the slopes :o) NOTE: Be sure to use cell references whenever possible. *=($D5/Q2unit!$B$5+$C5)M$4 No extra $ allowed Parentheses necessary in this case, but can distribute appropriately (okay)**
9	4		Jill is interested in improving her calories burned. Is this a What-If analysis or a Goal Seek? Give the answer you think is correct (What-If or Goal Seek), then define the other option (the one you didn’t choose as the answer). What-if Analysis Goal Seek definition should include SINGLE input value and DEFINED/KNOWN/GIVEN output value If get above wrong then definition of What-if should be: one or more input values with CALCULATED output value.

SCORE _____________/50