CSE200 SP07 KREEVES QUIZ#2 SEAT# ____________
NAME _______________________________________________ Lecture: TR 9:30-11:18am
Lab section (check one): _______ F 9:30-11:18am ________ F 11:30-1:18pm
Instructions:
· Filling in all the above data is worth 1 point.
· Put away all books, papers, and calculators.
· Turn off all beepers and cell phones.
· Do NOT get up for ANY reason unless you are finished with your test. If you have a question, please raise your hand; do NOT get up to ask a question of the TAs or the instructor. You will not be allowed to leave the room until you have completed your exam.
· 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. Failure to do so will result in point penalization.
· 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.
· Only use quotes when necessary – do NOT use quotes around Boolean values.
· Don’t use a $ if NOT copying.
· Only use the functions given.
· Your answer should update correctly when additional input data is added to the problem or when input data is changed.
PROBLEM DESCRIPTION:
ORDERS worksheet – This worksheet has input data (in gray) as well as problem solutions. Given are the name, club letter (A thru E), gender (M for male and F for female), size of the t-shirt ordered for each person as well as the cost. The size for each t-shirt starts with either the letter “C” for child or “A” for adult. For example, the size “CS” is a child small and the size “AS” is an adult small. There are five sizes for both child and adult sizes; they are S (small), M (medium), L (large), XL (extra large), and XXL (extra extra large).
CLUB worksheet – This worksheet defines information about each club (A-E) as designated on the ORDERS worksheet. Each club is listed here only once with questions being asked about each club.
CONVERT worksheet – This worksheet is used to convert dollar amount to other foreign currencies. Each value (in row 2) is designated as the amount of dollars equivalent to one of the given foreign currency. You will assume that the order you see the foreign currencies on this worksheet will be in the same order as you see them on the CLUB worksheet.
MATERIAL worksheet – This worksheet defines some unit conversions as well as the amount of material used in all the adult and child size t-shirts. The data in rows 1 thru 7 are input data. Notice that the information in the range B6:F7 designates the material used in square inches. This is very important to know when doing unit conversions.
1. (4 pts) Write an Excel formula in cell orders!F4, which can be copied down to cell orders!F14, to determine if Jolie is a child or not. The result should be TRUE if Jolie is a child and FALSE if Jolie is an adult.
=D4>"B" – best solution
=OR(D4=”CS”,D4=”CM”,D4=”CL”,D4=”CXL”,D4=”CXXL”)
Optional $ on column
2. (5 pts) Write an Excel formula in cell orders!G4, which can be copied down to cell orders!G14, to determine if Jolie’s t-shirt was backordered (TRUE) or not (FALSE). All the child size small t-shirts as well as the adult size extra extra large t-shirts were backordered.
=OR(D4="AXXL",D4="CS")
Optional $ on column
3. (7 pts) Write an Excel formula in cell orders!G16 to determine if only team A has at least one backordered t-shirt and none of the other teams have any t-shirts backordered. Use ranges whenever possible.
=AND(OR(G4,G11:G12),NOT(OR(G5:G10,G13:G14)))
No $ allowed (-2)
4. (7 pts) Write an Excel formula in cell orders!G17 to determine the percent of children that are on the list.
=COUNTIF(F4:F14,TRUE)/COUNT(E4:E14)
No $ allowed (-1)
5. (6 pts) Write an Excel formula in cell club!B2, which can be copied down to cell club!B6, to determine how many people are in club A.
=COUNTIF(orders!B$4:B$14,A2)
Optional $ on column
6. (6 pts) Write an Excel formula in cell club!C2, which can be copied down to cell club!C6, to determine the total cost of all the t-shirts ordered for club A. It does not matter if t-shirts are on backorder or not; they still have to pay for them!
=SUMIF(orders!B$4:B$14,A2,orders!E$4:E$14)
Optional $ on column
7. (6 pts) Write an Excel formula in cell club!D2, which can be copied down and across to cell club!G6, to determine the equivalent total cost of all the t-shirts for club A in euros. NOTE: see the CONVERT worksheet for conversion factors.
=$C2/convert!A$2
No extra $ allowed
8. (8 pts) Write an Excel formula in cell material!B10, which can be copied down and across to cell material!F11, to determine the cost per shirt based on the amount of material used (material!B6:F7) and the cost given in cell material!B3; other conversion factors may also be necessary to solve this problem.
=B6*($B$3/$B$2/$B$1)^2
SCORE _____________/50