CSE200 WI10 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, calculators, cell phones and music devices.

·     Turn off all beepers, cell phones and anything else that you can turn off that might make noise.

·     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.

·         Please stay in the answer box for each question i.e. do not write an essay for a short answer problem!

·     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 and appropriate.

·     Don’t use a $ if NOT copying.

·         Only use the functions given.

·         Do NOT put quotes around Boolean values.

·    Do not use unnecessary functions, for example =average(sum(a1:a5)). If you use an extra function that is not needed (the “average” function in the given example), it’s assumed that you thought it was necessary thus will be marked incorrect.

·         Caveat/Reminder to above: Do not use =IF(cond, TRUE, FALSE) as the IF structure in this case is unnecessary.

·         Do NOT use a nested IF function when a reference function will work since the reference function is a better more updatable less complex solution.

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

 

 

CSE200 WI10 KREEVES           ANSWER SHEET QUIZ#3            SEAT# ____________

 

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

 

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

 

 

TYPES – The input data is given in gray which includes A5:B16, D2:H4 as well as I5:I16. Each person has an experience level as specified in B4:B16.  The rest of the information is determined by the questions given below.

 

RUNS – All of the data on this worksheet is given i.e. input data. Included here in cells A2:A6 which have the same meaning as the information in column B of the above worksheet. However, in this case, the level is specified as a range of values. That is, for A2, the level is 0 only and has an associated type of “none” and there are 0 runs on the course for this type of skier i.e. a skier at this level; for A3, the levels 1 to less than 4 have the associated type of “beginner” and there are 4 runs on the course for this type of skier; for A4, the levels 4 to less than 9 have the associated type of “intermediate” and there are 6 runs on the course for this type of skier; for A5, the levels 9 to less than 15 have the associated type of “advanced” and there are 5 runs on the course for this type of skier; for A6, the levels greater than or equal to 15 have the associated type of “expert” and there are 6 runs on the course for this type of skier.

 

*** NOTE: You might think that types!E2:H4 and runs!A2:C6 are similar, but notice that the range on the TYPES worksheet is missing the “none” level so be very careful which range you use in the problems below.

 

ACRES – The cost per acre is given here in cell B1 as well as the size of some possible ski areas that you are looking into buying in cells B4:D4. The rest of the information is calculated by the question given below.

 

 

Q#

PTS

MINUS

QUESTION and ANSWER

1

8

 

Write an Excel formula in types!C5, which can be copied down to types!C16, to determine the type of skier jack is based on the data given in B5.

 

=IF(B5=0,"none",HLOOKUP(B5,E$2:H$3,2,TRUE))

=VLOOKUP(B5,runs!A$2:B$6,2,TRUE)

 

Optional $ on column

4th argument can be defaulted i.e. no 4th argument okay

No quotes allowed around 4th argument, if given

The IF structure can have the condition B5<>0 or B5>0 and then the arguments would be reversed/switched

 

 

2

8

 

Write an Excel formula in types!D5, which can be copied down to types!D16, to determine the number of runs that jack can ski on that are associated with the type of skier jack is (i.e. do NOT use the level as the lookup value, but use the type instead).

=VLOOKUP(C5,runs!B$2:C$6,2,FALSE)

=IF(C5=”none”,0,HLOOKUP(C5,E$3:H$4,2,FALSE))

condition can be C5<>”none” with 2nd/3rd  arguments switched

 

Optional $ on column

4th argument can be defaulted if true i.e. no 4th argument

No quotes allowed around 4th argument, if given

 

 

3

8

 

Write an Excel formula in type!E5, which can be copied down and across to types!H16, to determine if jack can make all of the beginner runs or none of them based on his level. Notice that a skier can make the runs for the given type if the skier’s level is greater than or equal to the level associated with the given type.

 

=IF($B5>=E$2,E$4,0)

=IF($B5<E$2,0,E$4)

=IF($B5>=E$2,VLOOKUP(E$3,runs!$B$2:$C$6,2,FALSE),0)

Condition can be $B5<E$2 with 2nd/3rd arguments switched

No extra $ allowed

 

NOTE: THE VALUES IN I5:I16 HAVE BEEN FILLED IN FOR YOU. THEY ARE THE TOTAL NUMBER OF RUNS THAT EACH PERSON IS ALLOWED TO SKI ON BASED ON THEIR LEVEL/TYPE.

 

4

8

 

Write an Excel formula in types!J5, which can be copied down to types!J16, to determine jacks description as defined below:

·         Jack is an “animal” if he can ski on 20 or more runs

·         Jack is “crazy” if he can ski on at least 9 runs but less than 20 runs

·         Jack is having “fun” if he can ski on less than 9 runs

 

Since can’t use AND/OR functions, the following two are the only solutions:

=IF(I5>=20,"animal",IF(I5>=9,"crazy","fun"))

=IF(I5<9,”fun”,IF(I5<20,”crazy”,”animal”))

Not allowed to have more than two IF structures

Optional $ on column

 

 

5

8

 

Write an Excel in acres!B5, which can be copied across to acres!D5, to determine the monthly payment for a loan that you are taking to buy the 1000 acres ski area. The cost of each acre is given and, along with the size of the ski area, defines the amount of the loan you will need. The interest rate is 6% annual interest compounded monthly and you want to pay off the loan in 15 years.

 

=PMT(6%/12,15*12,B4*$B1)

Optional $ on row

 

6

8

 

The above problem tells you how much you will need to spend each month for 15 years to pay the loan off so that you can own each ski area (based on size). Now, write an Excel formula in acres!B6, which can be copied across to acres!D6, to determine how much you would earn in 5 years time if you invested that amount of money each month for five years  (instead of getting a loan) at 10% annual interest compounded quarterly.

 

 

=FV(10%/4,5*4,B5*3)

Optional $ on row

 

 

SCORE _____________/50