CSE200 AU09 KREEVES QUIZ#3 SEAT# ____________
NAME _______________________________________________ Lecture: TR 9:30-11:18am
Lab section (check one): _______ F 9:30-11:18am ________ F 11:30-1: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 AU09 KREEVES ANSWER SHEET QUIZ#3 SEAT# ____________
NAME _______________________________________________ Lecture: TR 9:30-11:18am
Lab section (check one):
_______ F
9:30-11:18am
________ F 11:30-1:18pm
Q3OUTINGS – The input data is given in gray
which includes A4:E12 as well as G2. Each trip option has a type (raft, rock or trail) along with a numeric difficulty level (column
C) and a trip length (column D) with the designation of the type of length in
column E. The rest of the information is determined by
the questions asked below.
Q3LEVELS – All of the data on this worksheet is given i.e. input data. Included here in
cells A1:F3 are numeric/integer difficulty levels that are each associated with
a description. Both ranges, B1:F1 and B3:F3, have values that each
designate a range of values. B1 and B3 represent the
difficulty levels from 0 to less than 3 (values 0, 1 and 2); C1 and C3
represent the difficulty levels from 3 to less than 4 (value 3 only); D1 and D3
represent the difficulty levels from 4 to less than 6 (values 4 and 5); E1 and
E3 represent the difficulty levels from 6 to less than 10 (values 6, 7, 8 and
9); F1 and F3 represent the difficulty levels greater than or equal to 10.
Remember, though, that the values in B1:F1 and B3:F3 could change although the
values in both ranges will always be equal. They are both there, copies of each
other, for a reason :o)
The range A5:B9 represents the percent of difficulty level for
someone with the given description. The idea is that the harder a trail, climb or rafting trip, the higher it’s difficulty –
something you will be determining along the way.
|
Q# |
PTS |
MINUS |
QUESTION and
ANSWER |
|
1 |
8 |
|
Write an Excel formula in cell q3outings!F4,
which can be copied down to cell q3outings!F12, to determine the matching
difficulty level description associated with the given integer difficulty
level value of 0 for the first trip option. HLOOKUP(C4,q3levels!B$1:F$2,2) Optional true 4th argument – no
quotes allowed Optional $ on column |
|
2 |
8 |
|
Write an Excel formula in cell q3outings!G4,
which can be copied down and across to
cell q3outings!H12, to determine the number of miles associated with the
first trip option. NOTE: Some of the trip option lengths are
designated in miles and some are designated in feet (see column E). In
the associated column G is the number of miles for
each trip and in column H is the number of feet for each trip. As you copy
down and across, you are determining both the miles and feet for each trip
option. One of those options will be the given
data (from column D) and the other column will need to use the
conversion factor in G2 to convert the given data from feet to miles or miles
to feet (depending on the original information given in column
E). HINT: Notice that there are 3
possible results for this solution as underlined and italicized above. =IF($E4="miles",IF(G$3="miles",$D4,$D4*$G$2),IF(G$3="miles",$D4/$G$2,$D4)) =IF($E4=G$3,$D4,IF($E4="miles",$D4*$G$2,$D4/$G$2)) There are other similar solutions with a little different
ordering No optional $ |
|
3 |
8 |
|
Write an Excel formula in cell q3outings!I4, which can be copied down
and across to cell q3outings!J12, to determine what the first trip truly
feels like in miles when considering the difficulty level of the trip. NOTE:
the q3levels worksheet has the percent of difficulty increase based on the
difficulty description. =G4+VLOOKUP($F4,q3levels!$A$5:$B$9,2,FALSE)*G4 =(G4+1)*VLOOKUP($F4,q3levels!$A$5:$B$9,2,FALSE) No optional $ No quotes around FALSE allowed |
4 |
8 |
|
Write an Excel formula in cell q3outings!K4,
which can be copied down to cell q3outings!K12, to determine YES or NO, if
the first trip option gets a group discount. Group discounts are given to trips whose difficulty levels are less than
the integer designation for the “hard” description difficulty level. NOTE:
you cannot use the difficulty level integer itself as it may change and no
longer be associated with the “hard” description difficulty level; and you
can’t use a specific cell reference since the order of the words and the
number of words may change… you have to “look” for the word “hard”! =IF(C4<HLOOKUP("hard",q3levels!A$2:F$3,2,FALSE),"yes","no") Can have “no” as 2nd argument and “yes” as 3rd if
>= relational operator Optional $ on column |
|
5 |
8 |
|
Write an Excel formula (not shown) to determine how long in years it will take to pay the following loan
back… A bank loan of $700,000 was given to get the
Excel Adventures Company for equipment, land, hiring, and all the other
expenses needed to startup a new business. The annual interest rate was 8.5%
compounded monthly and the payments for paying back the loan were guaranteed to be $10,000 per month. =NPER(8.5%/12,-10000,700000)/12 No $ allowed ;o) No extra commas allowed Okay if used .085 instead of 8.5% Okay if used |
|
6 |
8 |
|
Ten years ago, an anonymous donor invested $100,000 at 12% annual
percentage rate compounded quarterly. The goal was to have money available to
help the Excel Adventures Company with their loan. Write an Excel formula
(not shown) to determine how much that investment is worth today. =FV(12%/4,10*4,0,-100000) No $ allowed ;o) No extra commas allowed Okay if used .12 instead of 12% Okay if used .03 instead of 12%/4 |
SCORE
_____________/50