CSE200 AU06
KREEVES
QUIZ#3
SEAT# ____________
NAME
_______________________________________________
Lecture: TR 1:30-3:18pm
Lab section
(check one) 1 pt:
_______
R 3:30-5:18pm
________ F 1:30-3:18pm
Instructions:
·
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. 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.
·
Don’t use a $ if NOT
copying
·
Only use the
functions given.
·
Do NOT put quotes
around Boolean values.
·
Your answer should
update correctly when additional
input data is added to the problem or when input data is changed.
·
Your solutions
shouldn’t be overly complex. For
example, don’t use an IF structure when a relational expression will do
EX.
=IF(B3>0,TRUE, FALSE)
FV(rate,
nper, pmt, pv, type) NPER (rate,
pmt, pv, fv, type) PMT(rate,
nper, pv, fv, type) PV(rate,
nper, pmt, fv, type) RATE(nper,
pmt, pv, fv, type) CANDY AVERAGE(number1, number2, …) COUNT(range) COUNTIF(range,
criteria) MAX(number1, number2, …) MIN(number1, number2, …) ROUND(number,
num_digits) SUM(number1, number2, …) SUMIF(range,
criteria, sum_range)
1. (10 pts) Write an Excel formula in cell F4, which can be copied down, to determine whether candy corn is a low, medium or high fat level candy that is bad for you (bad 4u). Use a reference function. HINT: You will need the fat level worksheet.
=HLOOKUP(E4,fatlevel!B$1:D$2,2,TRUE)
Optional $
on column
Optional
true value for 4th argument
2. (12 pts) Write an alternate solution to the Excel formula in cell F4, which can be copied down, but use nested IF structures instead of a reference function. Use ONLY cell references!
=IF(E4<fatlevel!C$1,fatlevel!B$2,IF(E4<fatlevel!D$1,fatlevel!C$2,fatlevel!D$2))
=IF(E4<fatlevel!C$1,fatlevel!B$2,IF(E4>=fatlevel!D$1,fatlevel!D$2,fatlevel!C$2))
=IF(E4>=fatlevel!D$1,fatlevel!D$2,IF(E4>=fatlevel!C$1,fatlevel!C$2,fatlevel!B$2))
=IF(E4>=fatlevel!D$1,fatlevel!D$2,IF(E4<fatlevel!C$1,fatlevel!B$2,fatlevel!C$2))
=IF(AND(E4>=fatlevel!C$1,E4<fatlevel!D$1),fatlevel!C$2,
IF(E4<fatlevel!C$1,fatlevel!B$2,fatlevel!D$2))
=IF(AND(E4>=fatlevel!C$1,E4<fatlevel!D$1),fatlevel!C$2,
IF(E4>=fatlevel!D$1,fatlevel!D$2,fatlevel!B$2))
Optional $
on column
3. (12 pts) Write an Excel formula in cell G9 to determine the name of the candy with the highest number of fat grams.
=VLOOKUP(MAX(E4:E8),E4:G8,3,FALSE)
No
$ allowed
4. (10 pts) Write an Excel formula (result not shown) to determine the monthly payment for a loan of $50,000 over a 5 year period with an interest rate of 8% compounded monthly.
=PMT(8%/12,5*12,50000)
No
$ allowed
Okay if 8% is .08
Okay
if put 60 instead of 5*12
5. (5 pts) Write an Excel formula (result not shown) to determine the same as above except determine a quarterly payment, instead of a monthly payment. NOTE: there are 3 months in a quarter. NOTE: don’t just tell me what is different from the above, but write out the entire formula for this problem.
Same as
above but *3 at the end (compound rate did not
change)
=PMT(8%/12,5*12,50000)*3
No
$ allowed
SCORE ___________/50