CSE200 WI09 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 in
name/seat#/lab information correctly is worth 1 point.
· Put away all books,
papers, and calculators.
· Turn
off all beepers and cell phones.
· 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.
· 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.
· Don’t use a $ if NOT copying
· Only use the functions given. Be
sure not to use IF structures for this exam.
· Your answer should update correctly when additional input
data is added to the problem or when input data is changed. That is, all
answers given should be “updatable” unless otherwise noted.
·
Only use quotes
when necessary – do NOT use quotes around Boolean values.
NOTE: You
MUST turn in ALL the pages of your exam or it will not be graded.
CSE200 WI09 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
1.
(5 pts) Which of the following formulas will work
to determine if all of the orders have less than 3 scoops? Circle YES if the
formula given will correctly determine if all the orders have less than 3
scoops.
YES NO 1 pt =MAX(F3:F10)<3
YES NO 1 pt =COUNTIF(F3:F10,">=3")=0
YES NO 1 pt =COUNTIF(F3:F10,1)+COUNTIF(F3:F10,2)=COUNT(F3:F10)
YES NO 2 pt =AND(F3:F10<=2)
2.
(6 pts) Write an Excel formula in cell SCOOP!H3, which can be copied down and across to SCOOP!J10, to
determine if a sugar cone was used for the raspberry chip flavor order.
=$G3=H$2
No extra dollar signs allowed
3.
(3 pts)
Write an Excel formula in cell SCOOP!H11, which can be
copied across to SCOOP!J11, to determine if someone ordered a sugar cone.
=OR(H3:H10)
=COUNTIF(H3:H10,TRUE)>0 also >=1
=COUNTIF(H3:H10,FALSE)<COUNT($D4:$D10)
optional $; can use <> instead of <
Optional $ on row
Can use column G for countif…
4.
(5 pts)
Write an Excel formula in cell SCOOP!I12 to determine
if only
sorbet orders use a waffle cone. NOTE:
This question is considered “not updatable” to a certain degree. That is, cells
SCOOP!B3:B10 will not change, however, your answer
should be able to handle the cone type changing.
=AND(OR(I7,I10),NOT(OR(I8:I9,I3:I6)))
=AND(I7,I10,NOT(I8),NOT(I9),NOT(I3),NOT(I4),NOT(I5),NOT(I6))
Can say I7=true (remember case insensitive)
Can say I8=false instead of NOT(I8)
No $ allowed
5.
(7 pts) Write an Excel formula in cell SCOOP!K3, which can be copied down to cell SCOOP!K10, to
determine if the raspberry chip ice cream order is healthy (true or false). An
order is considered healthy if it uses 1 or 2 small scoops and has type sorbet
or frozen yogurt.
=AND(OR(B3="sorbet",B3="frozen
yogurt"),C3="small",F3<3)
Can use D4=3 instead of C3=”small”
F3<3 can also be OR(F3=1,F3=2)
Optional $ on column
6.
(3 pts) Write an Excel formula in cell SCOOP!K13 to determine if all of the orders are considered
healthy.
=AND(K3:K10)
=COUNTIF(K3:K10,FALSE)=0
=COUNTIF(K3:K10,TRUE)=COUNT(D3:D10)
also columns E and F can be used on the COUNT
No $ allowed
7.
(7 pts) Write an Excel formula in cell SCOOP!K14 to determine the percent of orders that are considered
healthy. The cell is already formatted for percent.
=COUNTIF(K3:K10,TRUE)/COUNT(F3:F10)
=COUNTIF(K3:K10,TRUE)/(COUNTIF(K3:K10,TRUE)+COUNTIF(K3:K10,FALSE))
No $ allowed
8.
(7 pts) Write an Excel formula in cell SCOOP!L3, which can be copied down to cell SCOOP!L10, to
determine the total number of calories for the scoops of raspberry
chip ice cream. NOTE: Notice that in cell E2, the label shows the “calories per
½ cup”. This is your starting point. Be sure to include the ½ cup designation
somehow in your solution; and don’t forget to check the UNIT worksheet for more
information.
=2*E3/unit!A$1*D3*F3
Order doesn’t matter as long as correct
operator in front of each cell reference
Optional $ on column
9.
(6
pts)
Write an Excel formula in cell CALORIES!B2, which can
be copied down to cell CALORIES!B4, to determine the total calories for all the
frozen yogurt orders.
=SUMIF(scoop!B$3:B$10,A2,scoop!L$3:L$10)
Optional $ on column
SCORE _______________/50