CSE200 WI10 KREEVES                                  QUIZ#1                              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.

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

 

TRUE/FALSE (1 pt each = 12 points total) 

GREEN highlighted questions are TRUE; all others are FALSE

 

1.      The Internet is a good example of a wide area network.

 

2.      This is the correct ordering of memory size from largest to smallest: bit, byte, kilobyte, megabyte, gigabyte.

 

3.      It is never okay to have two files with the same name stored on your CSE account.

 

4.      Primary memory, which is also called RAM, permanently stores information so that it’s readily available to the CPU. 

 

5.      The #REF! Excel error value means that the cell value is too big to fit in the column width.

 

6.      Given the same configuration, a laptop computer is less expensive and more portable than a desktop computer. 

 

7.      Operating System software are computer programs written to perform a specified task.

 

8.      The ordering of websites that result from specified search criteria/keywords is a random process.

 

1.      Obtaining peripheral devices is a very important part of the computer buying process.

 

2.      A faster clock speed will help speed up your processing even if the bus speed is slow.

 

3.      You can access OSU webmail by using Windows Explorer.

 

4.      =MIN(A1:A5) yields the same result as =SMALL(A1:A5,5)

FUNCTIONS                                         MIN(number1,number2,…)  

AVERAGE(number1,number2,…)   RANK( number, ref, order)

COUNT(number1,number2,…)        ROUND(number, num_digits)

COUNTIF(range, criteria)                   SMALL(array,k)

LARGE(array,k)                                    SUM(number1,number2,…)

MAX(number1,number2,…)              SUMIF(range, criteria, sum_range)
 

The INPUT data for this problem is given in gray.

A5:E9,

A11:A12, and

B11
 

 

 

 

 

Remember you should be using ranges NOT lists of cell references since using the range is updatable and the use of lists is not.

 

5.      (4 pts) Write an Excel formula in cell B12 to determine how many groups are currently at the Excel-lent Ski Lodge and Resort.

 

=COUNT(B5:B9) can also use column C, D, E

No $ allowed since not copying

Not allowed to use the COUNTA function

 

6.      (6 pts) Write an Excel formula in cell C11, which can be copied down and across to cell E12, to determine the highest cost in the list of “lift & rentals” prices. NOTE: As you copy across, you should automatically be determining the highest cost in the list of “lodging” then “car rental” prices respectively. As the formula is copied down, you should be determining the lowest price (i.e. the 5th highest – see cell B12 – in this case) per column.

 

=LARGE(C$5:C$9,$B11)

No extra $ allowed

 

7.      (6 pts) Write an Excel formula (cell not shown) to determine the number of groups that are renting cars. NOTE: A zero will be denoted for groups not renting a car i.e. they aren’t paying anything for a car rental since they aren’t renting a car ;o) The return value of the solution for the given data is 4.

 

=COUNTIF(E5:E9,">0")

No $ allowed

 

8.      (6 pts) Write an Excel formula in cell F5, which can be copied down and across to cell H9, to determine the percent of “life & rentals” costs a person will pay in relation to the total cost. NOTE: Total cost includes the lift & rentals, lodging and car rental costs together.

 

=C5/SUM($C5:$E5)

No extra $ allowed

 

9.      (2 pts) Explain why the values in F5:H5 do not add up to 100%.

 

The PRECISION of the values do add up to 100% even though the FORMAT of those same values do not.

 

10.  (6 pts) Write an Excel formula in cell I5, which can be copied down and across to cell K9, to determine the ordering for all the lift & rental percents per person per day values based on how expensive the lift & rentals for Group A are in comparison to the other groups. NOTE: Group A has the 3rd least expensive cost for lift & rentals, whereas Group B has the least or minimum or 1st least expensive cost for lift & rentals.

 

=RANK(F5,F$5:F$9,1)

No extra $ allowed

Cannot use column C instead of column F (gives different solution)

 

 

11.  (6 pts) Write an Excel formula in cell L5, which can be copied down to cell L9, to determine the average rank of lift & rentals, lodging and car rentals for Group A rounded to the nearest tenths place.

 

=ROUND(AVERAGE(I5:K5),1)

Okay if SUM(I5:K5)/COUNT(I5:K5) instead of AVERAGE

Count function range can also use F5:H5 or C5:E5 instead of I5:K5

Cannot use the RANK function in this solution

Optional $ on column