CSE200 WI07 KREEVES QUIZ#3 SEAT# ____________
NAME _______________________________________________ Lecture: MW 9:30-11:18am
Lab section (check one): _______ R 9:30-11:18am ________ R 11:30-1: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 and no points awarded 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.
·
Your answer should update correctly when additional
input data is added to the problem or when input data is changed, whenever
possible.
·
Your solutions shouldn’t be overly complex.
CSE200 WI07 KREEVES QUIZ#3 SEAT# ____________
NAME _______________________________________________ Lecture: MW 9:30-11:18am
Lab section (check one): _______ R 9:30-11:18am ________ R 11:30-1:18pm
For each problem,
if multiple answers are given, please CIRCLE the correct answer.
1.
(6 pts) Write an
Excel formula in cell college!F5, which can be copied down to college!F12, to
determine whether PSU is on the semester system or the quarter system. NOTE: When
there are 2 divisions per year, the school is on the semesters system; when there are 3 divisions per year, the
school is on the quarters
system.
=IF(E5=2,"semesters","quarters")
=IF(E5=3,"quarters","semesters")
2. (4 pts) Write an Excel formula in cell college!G5,
which can be copied down to college!G12, to determine the total cost per year
for tuition, room and board at PSU. Make sure your answer is updatable; still
works if add a college.
=SUM(B5:D5)
3.
(10 pts) Write an
Excel formula in cell college!H5 using
a nested IF, which can be copied down to college!H12, to determine your
rating for PSU. You have decided that:
- A rating of “high” (likely to attend) will be
given for colleges whose total cost per year is less than $30,000
- A rating of “low” (unlikely to attend) will be
given for colleges whose total cost per year is $40,000 or higher
- Otherwise, a rating of “mid” (may attend) will be
given.
Be
sure to use cell references only (see the rate worksheet).
=IF(G5>=rate!D$1,rate!D$2,IF(G5>=rate!C$1,rate!C$2,rate!B$2))
=IF(G5>=rate!D$1,rate!D$2,IF(G5<rate!C$1,rate!B$2,rate!C$2))
=IF(G5<rate!C$1,rate!B$2,IF(G5<rate!D$1,rate!C$2,rate!D$2))
=IF(G5<rate!C$1,rate!B$2,IF(G5>=rate!D$1,rate!D$2,rate!C$2))
=IF(AND(G5>=rate!C$1,G5<rate!D$1),rate!C$2,IF(G5<rate!C$1,rate!B$2,rate!D$2))
=IF(AND(G5>=rate!C$1,G5<rate!D$1),rate!C$2,IF(G5>=rate!D$1,rate!D$2,rate!B$2))
4. (8 pts) Write a different Excel formula to solve for
the above problem using a reference
function.
=HLOOKUP(G5,rate!B$1:D$2,2,TRUE)
True is optional i.e. can leave off the comma and the TRUE value
5. (8 pts) Write an Excel formula in cell college!I14 to
determine which school has the highest total cost per year.
=VLOOKUP(MAX(G5:G12),G5:I12,3,FALSE)
MAX(G5:G12) is the same as LARGE(G5:G12,1)
6. (6 pts) Your parents started a college fund for you 10
years ago. They put in $1000 per month into the college fund at 10% annual
interest rate compounded monthly. Write an Excel formula in cell college!I15 to
determine how much money your parents have saved to put you through college.
=FV(10%/12,10*12,-1000)
Optional zeroes for arguments 4 and/or 5
Okay to put .1 or .10 instead of 10%
7. (7 pts) Unfortunately, the money your parents saved
did not cover all the costs for going to the school you wanted to attend… OSU
:o) Write an Excel formula in cell college!I16 to determine the monthly payment
you owe to pay back the loan in 10 years with annual interest rate of 5%
compounded yearly. NOTE: Your total cost for attending OSU is the total cost
per year for 4 years. What you owe is the difference between this total 4 year cost
and the money your parents saved for you.
=PMT(5%,10,G6*4-I15)/12
Optional zeroes for arguments 4 and/or 5
Okay to put .1 or .10 instead of 10%
SCORE ____________/50