CSE200 SP09
KREEVES QUIZ#3
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, 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.
· Do NOT put quotes around Boolean values.
· Do not use =IF(cond, TRUE, FALSE) as the IF structure in this case is unnecessary.
· Your answer should update correctly when additional input data is added to the problem or when input data is changed (unless where specifically noted otherwise).
CSE200 SP09
KREEVES QUIZ#3
SEAT# ____________
NAME _______________________________________________ Lecture: TR 1:30-3:18pm
Lab section (check one): _______ F 1:30-3:18pm ________ F 3:30-5:18pm
1.
(8 pts) Write an Excel formula in cell
Q3input!D4, which can be copied down to Q3input!D22, to determine if john plays
“infield” or “outfield” based on only the input data given on the Q3input
worksheet. An infielder plays in positions 1 thru 6 and an outfielder plays in
positions 7 thru 9.
=IF(C4<=6,"infield","outfield")
=IF(C4>6,”outfield”, "infield")
Optional
$ on column
Can
put AND(C4>=1,C4<=6) for first IF as well as
AND(C4>6,C4<=9)
Remember
C4<=6 is the same as C4<7 (since integer values) and C4>6 is the same
as C4>=7
2.
(8 pts) Write an Excel formula in cell
Q3input!E4, which can be copied down to Q3input!E22, to determine (true or false)
if john plays an infield position based on the data given in the Q3pos
worksheet
=VLOOKUP(C4,Q3pos!A$2:C$10,3)
Optional
TRUE 4th argument (since in ascending order)
FALSE
can also be used as the 4th argument since it’s an exact match for
position number
Optional
$ on column
3.
(8 pts) Using a reference function and
the data given on the Q3leag worksheet, write an Excel formula in cell Q3input!F4, which can be copied down to Q3input!F22, to determine what
league john is in.
=HLOOKUP(B4,Q3leag!B$1:D$2,2,FALSE)
Optional
$ on column
4.
(8 pts) Using a nested IF function and
the data given on the Q3leag worksheet, write an Excel formula in cell Q3input!F4, which can be copied down to Q3input!F22, to determine
what league john is in. NOTE: Be sure to use cell references whenever possible.
=IF(B4=Q3leag!C$1,Q3leag!C$2,IF(B4=Q3leag!D$1,Q3leag!D$2,Q3leag!B$2))
Optional
$ on column
Optional
solutions è make sure the comparison value is row 1 and
the result is the same col row 2
5.
(8 pts) Write an Excel formula (not
shown but on the Q3input worksheet) to determine the yearly payments for a
$2,000,000 loan taken out by the jay-excel franchise baseball team for a new
ball park. The details of the loan include an annual interest rate of 5%
compounded quarterly over a 10 year period.
=PMT(5%/4,10*4,2000000)*4
No
$ since not copying
Ok
if put 40 instead of 10*4, etc
6.
(8 pts) Investor’s have been making it
possible for the jay-excel franchise baseball team to invest an average of $150,000
a quarter into an investment account. Write an Excel formula (not shown but on
the Q3input worksheet), to determine (true or false) if after 10 years, the team will have enough money to pay for
the $2,000,000 ball park given the same annual interest rate of 5% but compounded
monthly?
=FV(5%/12,10*12,-150000/3)>=2000000
Also =PMT(5%/12,10*12,2000000)*3 +
150000 >= 0
Also =0-PMT(5%/12,10*12,2000000)*3<=150000
No
$ since not copying
Ok
if put 120 instead of 10*12, etc
SCORE _____________/50