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.

 

 

 

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.

 

 

 

 

 

 

 

 

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.

 

 

 

 

 

 

 

 

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”!

 

 

 

 

 

 

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.

 

 

 

 

 

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.

 

 

 

 

 

 

 

SCORE _____________/50