CSE200 SP08 KREEVES                                  QUIZ#3                             

 

Instructions:

·         Filling in name/seat#/lab information correctly 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.

·        Your answer should update correctly when additional input data is added to the problem or when input data is changed.

·         All answers given should be “updatable” unless otherwise noted (see above bullet).

·         Do not use extra IF structures.  An example of using an extra IF structure is IF(cond,true,false) when just the condition would be the correct answer.

·         Only use quotes when necessary – do NOT use quotes around Boolean values.

 

 

 

TUTORS
 

FUNCTIONS

IF(logical_test,value_if_true, value_if_false)

HLOOKUP(lookup_value, table_array, row_index_no, type)

VLOOKUP(lookup_value, table_array, col_index_no, type)

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)
 
               

 

HRCOST
 

REFTYPE
 
                                                     

 

 

 

NOTE: YOU CAN KEEP THIS PAGE BUT BE SURE TO TURN IN THE PAGE WITH THE ANSWERS ON IT :O)

 

CSE200 SP08 KREEVES                                  QUIZ#3                              SEAT# ____________

NAME _______________________________________________        Lecture:  TR 1:30-3:18pm 

 

Lab section (check one):            _______ R 3:30-5:18pm             ________ F 1:30-3:18pm

 

1.      (8 pts) Write an Excel formula in cell tutors!D3 without using a reference function, which can be copied down to cell tutors!D9, to determine what type of tutor jon is based on his rank. The types are determined by the following bulleted data and remember that you can only use the functions given.

·         The type is “U” for undergrad if the rank is between 1 and 4, inclusive

·         The type is “G” for grad if the rank is either 5 or 6

·         The type is “O” for other if the rank is either 7 or 8

 

=IF(C3<=4,"U",IF(C3<=6,"G","O"))

=IF(C3<5,"U",IF(C3<7,"G","O"))

=IF(C3>=7,"O",IF(C3>=5,"G","U"))

=IF(C3>6,"O",IF(C3>4,"G","U"))

Optional $ on column

No AND/OR functions allowed

Okay if used HRCOST!  type cell references (hrcost!b1, etc) instead of “O”, “G”, and “U”

Additional IF structures are given a deduction

 

2.      (8 pts) An alternate method can be used to determine the solution to the above problem. Fill in the below given worksheet named REFTYPE with the correct data and then use a reference function to solve the problem given above. NOTE: you cannot repeat the letter type (i.e. O, U, or G)

 

REFTYPE
 
 

 


=VLOOKUP(C3,reftype!A$2:B$4,2)

Optional $ on column

Cell A2 can also be a 1

Optional TRUE as 4th argument

Optional worksheet name in front of B$4

 

 

3.      (8 pts) Write an Excel formula in cell tutors!E3, which can be copied down to cell tutors!E9, to determine the cost/hr jon will earn based on the fact that he is a “other” type of tutor. Based on the HRCOST worksheet, an “other” tutor will earn $20/hr, a “grad” tutor will earn $15/hr and an “undergrad” tutor will earn $12/hr.

 

=HLOOKUP(D3,hrcost!B$1:D$2,2,FALSE)

Optional $ on column

Optional worksheet name in front of D$2

 

 

4.      (8 pts) Write an equivalent nested IF solution for the above problem.

 

=IF(D3="O",20,IF(D3="G",15,12))

=IF(D3="O",20,IF(D3="U",12,15))

=IF(D3="G",15,IF(D3="U",12,20))

=IF(D3="G",15,IF(D3="O",20,12))

=IF(D3="U",12,IF(D3="G",15,20))

=IF(D3="U",12,IF(D3="O",20,15))

Optional $ on column

Need to use cell references… I was lazy ;o)

 

5.      (8 pts) Jon invested $5000 4 years ago and would like to start a tutoring company with the money. Write an Excel formula to determine how much money Jon’s investment is now worth if the interest rate is 12% (annual percentage rate) compounded quarterly.

 

=FV(12%/4,4*4,0,-5000)

Zero can be deleted but would have two commas side by side

No comma allowed for -5,000

Optional 0 for fifth argument

 

6.      (8 pts) Jon got all the tutors together and they formed a company. The startup costs for the company are $15,000 (which is the loan amount). The company’s monthly income is $550 but they are only willing to pay a $300 monthly payment for the loan so they can use the leftover dollars for supplies, rent, etc. Write an Excel formula to determine how many years it will take to pay off the loan if the interest rate is 10% (annual percentage rate) compounded monthly.

 

=NPER(10%/12,-300,15000)/12

No comma allowed for 15,000

Optional 0 for fifth argument

 

                                                                                                                   SCORE ON BACK OF PAGE