CSE200 SP07 KREEVES QUIZ#3 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.
· Do not use an IF structure if it’s unnecessary ex. IF(A>3, TRUE, FALSE)
· 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 is the person’s name, club letter (A thru E), size of the t-shirt ordered as well as whether or not the person is a child (true if they are a child, false if they are an adult). An extra column of input data is in column H, a repeat of the name, to be used later for a specific reason.
COST worksheet – 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). The adult size costs are given as designated in row 2 and the child size costs are given as designated in row 3.
1. (9 pts) Write an Excel formula in cell orders!E4, which can be copied down to cell E14, to determine the original cost of the t-shirt that Jolie ordered.
=HLOOKUP(C4,cost!B$1:F$3,IF(D4,3,2),FALSE)
=IF(D4,HLOOKUP(C4,cost!B$1:F$3,3,FALSE),HLOOKUP(C4,cost!B$1:F$3,2,FALSE))
Okay if put D4=true
Okay if IF(d4=false,2,3)
Optional $ on column
2. (8 pts) Write an Excel formula in cell orders!E16 (not shown) to determine who ordered the most expensive t-shirt. FYI: the answer is “Jared”. NOTE: this is the most expensive original cost t-shirt, NOT the most expensive discount cost t-shirt (as this has not been solved for yet).
=VLOOKUP(MAX(E4:E14),E4:H14,4,FALSE)
No $ allowed
3. (9 pts) Write an Excel formula in cell orders!F4, which can be copied down to cell F14, to determine the discount t-shirt cost for Jolie. The discount percent is 20% off for clubs A and E, but no discount is given for the other clubs, so their discount cost will be the same as the original cost.
=IF(OR(B4="A",B4="E"),E4*(1-20%),E4)
=IF(OR(B4=”B”,B4=”C”,B4=”D”),E4,E4*(1-20%))
Okay if put E4-E4*20% instead of E4*(1-20%)
Optional $ on column
Can use .2 or .20 instead of 20%
4. (10 pts) Write an Excel formula in cell orders!G4, which can be copied down to cell G14, to determine the type of discount Jolie was given:
=IF(E4=F4,"none",IF(E4-F4>2,"big","small"))
=IF(E4=F4,"none",IF(E4-F4<=2,"small",”big”))
=
IF(E4-F4>2,"big",IF(E4=F4,”none”,"small"))
=
IF(E4-F4>2,"big",IF(E4-F4>0,"small",”none”))
=IF(AND(E4-F4>0,E4-F4<=2),”small”,IF(E4=F4,”none”,”big”))
=IF(AND(E4-F4>0,E4-F4<=2),”small”,IF(E4-F4>2,”big”,”none”))
Okay if put E4-F4=0 for “none” option
Optional $ on column
5. (6 pts) All 5 clubs want to open their own t-shirt making store so they don’t have to buy t-shirts anymore, but instead they want to make them. Write an Excel formula in cell orders!E18 (not shown) to determine the monthly payment for a loan taken out for $450,000 with a 5% interest rate compounded monthly for 5 years.
=PMT(5%/12,5*12,450000)
No $ allowed
6. (7 pts) Write an Excel formula in cell orders!E19 (not shown) to determine the monthly payment for a loan amount of $450,000 at 5% interest rate compounded quarterly for 5 years.
=PMT(5%/4,5*4,450000)/3
No $ allowed
SCORE _____________/50