CSE200 AU10 KREEVES                                  QUIZ#2                              SEAT# ____________

NAME _______________________________________________        Lecture:  MW 8:30-10:18am 

 

Lab section (check one):            _______ R 9:30am-11:18am             ________ R 11:30am-1:18pm

 

Instructions:

·    Filling out the correct seat# and lab section on both the test and the answer sheet is worth 2 points.

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

·        Put away all books, papers, calculators, cell phones and music devices.  There WILL BE a point penalization with the possibility of a score of ZERO for not adhering to these directions.

·        When time has run out you will be told to put all pens/pencils down.  There WILL BE a point penalization with the possibility of a score of ZERO for not adhering to these directions.

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

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

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

 

   

 WORKSHEET DESCRIPTIONS

 

The Excel-lent office supplies file has all the 2010 orders listed for your information as well as some calculations (which include other important data) for you to determine. The following worksheets are given in the file:

 

Q2cse –The input data is given in the range A5:H20. For each supply, the description for the type of supply being ordered along with the # of days to until another order needs to be made, followed by quantity, cost and weight (in ounces) per item is listed.  The last two columns in the input data range contain Boolean values where TRUE means that the item is re-usable or recyclable, respectively.

 

Q2unit – This worksheet has conversion factors needed to help with the calculation of new data. Be sure to use the data given here as a cell reference (not a constant value). I understand that some of these values do not actually change, however this is being used as a learning experience so please be sure to “use cell references whenever possible”.

 

NOTICE: The functions you can use for this exam are listed on the worksheet page i.e. You can only use the AND, OR, and NOT functions on this exam.

 

 

CSE200 AU10 KREEVES                                  QUIZ#2                              SEAT# ____________

NAME _______________________________________________        Lecture:  MW 8:30-10:18am 

 

Lab section (check one):            _______ R 9:30am-11:18am             ________ R 11:30am-1:18pm

 

Q#

PTS

MINUS

QUESTION and ANSWER

1

5

 

Write an Excel formula in cell Q2cse!H22 to determine (true or false) if all of the supplies/items on the list are both reusable and recyclable.

2

7

 

Write an Excel formula in cell Q2cse!H23 to determine (true or false) if only post-it notes are recyclable.  NOTE: The values in the description column (column B) cannot change, but everything else can change and your answer should still work.

3

7

 

Write an Excel formula in cell Q2cse!I5, which can be copied down to Q2cse!I20, to determine (true or false) if the cse1 binder clips can be re-used, re-cycled or automatically  reordered. An item/supply can be automatically re-ordered if the quantity is 100 or more and if the cost per item is less than one dollar.

4

5

 

Write an Excel formula in cell Q2cse!J5, which can be copied down and across to Q2cse!L20, to determine (true or false) if the cse1 binder clips need to be reordered every 45 days. NOTE: When the formula is copied across, the question then asks if the item/supply should be reordered every 90 days, then across again to be reordered every 365 days per values in cells J4:L4. The actual days to re-order for each item/supply is in column C.

5

7

 

Write an Excel formula in cell Q2cse!M5, which can be copied down to Q2cse!M20, to determine the shipping cost for the 50 quantity cse1 binder clips. NOTE: We cannot help you with the calculation on this problem as it gives the solution away; see the conversion factors on the Q2unit worksheet.

6

  7

 

You love your iphone very much ;o) The weight in grams for your iphone is on the Q2unit worksheet. Write an Excel formula in cell Q2cse!N5, which can be copied down to Q2cse!N20, to determine the number of iphones that can be shipped for the same price as the cse1 binder clips cost as determined in the previous problem. NOTE: We cannot help you with the calculation on this problem as it gives the solution away; see the conversion factors on the Q2units worksheet.

7

4

 

When considering the definitions for a goal seek and a what-if type of problem:

a.       What is the difference in the input values?

b.      What is the difference in the output values?

8

6

 

a.       What type of chart is shown on the worksheet page?

b.      Determine the range, from the Q2cse worksheet, that was used to create this chart.

c.       Why are percent values shown on the chart, but not given in the worksheet?

 

 

CSE200 AU10 KREEVES                                  QUIZ#2                              SEAT# ____________

NAME _______________________________________________        Lecture:  MW 8:30-10:18am 

 

Lab section (check one):            _______ R 9:30am-11:18am             ________ R 11:30am-1:18pm

 

Q#

PTS

MINUS

QUESTION and ANSWER

1

5

 

Write an Excel formula in cell Q2cse!H22 to determine (true or false) if all of the supplies/items on the list are both reusable and recyclable.

 

=AND(G5:H20)

=AND(G5:G20,H5:H20)

No $ allowed

 

2

7

 

Write an Excel formula in cell Q2cse!H23 to determine (true or false) if only post-it notes are recyclable.  NOTE: The values in the description column (column B) cannot change, but everything else can change and your answer should still work.

 

=AND(OR(H12,H14),NOT(OR(H5:H11,H13,H15:H20)))

No $ allowed

 

3

7

 

Write an Excel formula in cell Q2cse!I5, which can be copied down to Q2cse!I20, to determine (true or false) if the cse1 binder clips can be re-used, re-cycled or automatically  reordered. An item/supply can be automatically re-ordered if the quantity is 100 or more and if the cost per item is less than one dollar.

 

=OR(G5:H5,AND(D5>=100,E5<1))

Can have a comma instead of a colon between G5 and H5

Optional $ on column

 

4

5

 

Write an Excel formula in cell Q2cse!J5, which can be copied down and across to Q2cse!L20, to determine (true or false) if the cse1 binder clips need to be reordered every 45 days. NOTE: When the formula is copied across, the question then asks if the item/supply should be reordered every 90 days, then across again to be reordered every 365 days per values in cells J4:L4. The actual days to re-order for each item/supply is in column C.

 

=$C5=J$4

No extra $ allowed

 

5

7

 

Write an Excel formula in cell Q2cse!M5, which can be copied down to Q2cse!M20, to determine the shipping cost for the 50 quantity cse1 binder clips. NOTE: We cannot help you with the calculation on this problem as it gives the solution away; see the conversion factors on the Q2unit worksheet.

 

=D5*F5/q2unit!A$4*q2unit!A$2

Optional $ on column

 

Remember that on unit functions, the order doesn’t matter as long as the correct operator is in front of the correct cell reference.

6

  7

 

You love your iphone very much ;o) The weight in grams for your iphone is on the Q2unit worksheet. Write an Excel formula in cell Q2cse!N5, which can be copied down to Q2cse!N20, to determine the number of iphones that can be shipped for the same price as the cse1 binder clips cost as determined in the previous problem. NOTE: We cannot help you with the calculation on this problem as it gives the solution away; see the conversion factors on the Q2units worksheet.

 

=M5/q2unit!A$2*q2unit!A$1/q2unit!A$3

Optional $ on column

 

Remember that on unit functions, the order doesn’t matter as long as the correct operator is in front of the correct cell reference.

 

7

4

 

When considering the definitions for a goal seek and a what-if type of problem:

 

c.       What is the difference in the input values?

Goal seek has one and only one input; What-if has 1 or more

d.      What is the difference in the output values?

Goal seek has a given output; What-if a calculated output

 

8

6

 

d.      What type of chart is shown on the worksheet page?

Pie 

 

e.       Determine the range, from the Q2cse worksheet, that was used to create this chart.

=A5:B8, D5:D8

Optional $, optional worksheet name

 

f.        Why are percent values shown on the chart, but not given in the worksheet?

You can choose percentage labels for the pie chart so auto generated

 

 

SCORE _____________/50