D. Nixon: CS 12 > Week 2

Home (CS 12) | Assignments | Tutorials | Quizzes & Exam | Other Info

Computer Science 12


Week 2




Sunday, February 7, 2010



Tutorials on Excel

On Excel functions in general, and specifically the SUM, AVERAGE, COUNT, COUNTA, MIN, MAX, and MEDIAN functions:

Line breaks in cells:

On Excel charts:

On Excel tables:

It is recommended that you also use Excel's Help feature to get more information on all of the above.

See also the Excel tutorials listed for Week 1.


To all information for Week 2  |  To all tutorials about Excel



Assignment 2

  1. Paper:

    2. Continue work on your paper, as follows: (1) by no later than Thursday, February 11, about the topic you have chosen. (2) By no later than Thursday, February 18, email me your list of sources. (2) Submit the first draft via Blackboard, by no later than the beginning of class on Sunday, February 28. It should have the following filename:

    where "last" and "first" should be replaced by your own last name and and first name, respectively.

  2. Excel assignment:

    1. Making believe you're a teacher, create an Excel spreadsheet with columns as follows: (1) a list of names, (2) five columns of quiz scores, (2) two columns of results to be computed from quiz scores, (3) two columns of in-class exam scores, and (4) the final exam score. The two columns of results computed from the quiz scores must be (a) the average quiz score and (b) the average with lowest score dropped.

      2. Make rows for at least five students. The names of your imaginary students, and the quiz scores, will be left to your imagination. Just don't copy them from another student. The quiz scores should range between 0 to 10, while the exam scores should range between 0 and 100.

      Save your Excel spreadsheet with a filename having the following format:

      • last-first-roster.xlsx

      where "last" and "first" should be replaced by your own last name and and first name, respectively.

      Then format the "Grade Roster" title and "Contact" lines to make them look nice, merging adjacent cells as necessary to eliminate overlap. To the left of the scores, you must have columns for imaginary students' names, first a column for the last names, then a column for the first names. Make up a unique set of names (not used by other Computer Science 12 students). Above the scores, there must be a row for column headings for the scores. The column headings should be: Quiz 1, Quiz 2, Quiz 3, Quiz 4, Quiz 5, Exam 1, Exam 2, and Final Exam.

      Then, in between the quiz scores and the exam scores, insert two columns for calculated results involving the quiz scores. The first of these should be a column for the average quiz score. The second of these should be a column which computes an average of quiz scores with the lowest grade dropped. (Hint: Use a formula involving the SUM, COUNT, and MIN functions, not the AVERAGE function.)

      Below the rows for the students, add rows for (1) the class average for each quiz, exam, or calculated result, (2) the class's lowest score for each quiz, exam, or calculated result, and the class's highest score for each quiz, exam, or calculated result.

      Below the grade roster, create a column chart showing the lowest, average, and maximum scores for each quiz, exam, and calculated result. (The easiest way to do this will be to create a column chart for your entire roster, then right click on it and click "Select Data" to edit the chart, then delete unwanted parts. Also, before you create the chart, it is recommended that you temporarily remove any heading you have at the top of the column for students' names, so that the other column headings will be recognized as headings.) See this week's Excel tutorials for step-by-step instructions on creating charts.

    2. In a separate file, write an invoice for goods ordered from a store. The invoice should have columns for product name, per-unit price, quantity ordered, and total price. There should be rows for at least four products. Underneath the last row, below the total price column there should cells (with identifying text in cells to the left of them) for subtotal, tax, and then total. The tax should be computed from the tax rate, which should be placed in a separate cell somewhere.

      3. In order to do the tax computation, referring to the tax rate in some specific cell, you will need to use absolute addressing, so that your formula will still refer to the tax rate in the same cell when you drag the formula.

      All columns should have clear headings. And, somewhere above the headings, there should be cells for the store's name, the date, and the customer's name and address. Use two or more merged cells for the store's name, the customer's name, and each line of the customer's address, so that these will display nicely, and use an appropriate heading format for the store's name. Put your own name and email address in appropriately merged cells at the bottom.

      The invoice should have a filename with the following format:

      • last-first-invoice.xlsx

      where "last" and "first" should be replaced by your own last and first name.

      Your invoice must be unique, containing a store name, product names, and prices different from those in files submitted by other students in Computer Science 12.

    Submit BOTH of the above files in the appropriate area on Blackboard, and have access to the files in lab next week. (The two files should be sumitted separately in the same area on BlackBoard, not in a ZIP file. You must submit both at once, not separately.) We will use them again in future exercises both in lab and in homework.


To all information for Week 2  |  To all homework assignments

D. Nixon: CS 12 > Week 2