D. Nixon: CS 12 > Assignments

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

Computer Science 12


Assignments (in-lab and homework)




Assignment 1 (see distinct due dates for each part)

  1. Paper on problem-solving, part 1 (due Sunday, March 1, 2020; preliminary email due ASAP NEXT WEEK)
  2. Scratch assignment (due before next class, February 9)
  3. Excel (in-class demo only)
  4. Powerpoint slide show, part 1 (due Sunday, February 23)

  1. Paper on problem-solving, part 1 (due Sunday, March 1, 2020; preliminary email due ASAP NEXT WEEK):
  2. The paper on problem-solving is a multi-part term project. The first four parts will count as normal homework assignments, and then the final version will count 4% of your total score.

    Your paper will discuss the process of figuring out how to solve various problems in Excel. For some examples of such discussions, see:

    Note how that second page first discusses several simpler related problems, and also presents a mistaken attempt at a solution, explaining what the error is, before presenting the complete correct solution. Often, when figuring out how to solve a problem, it is helpful to consider simpler variants of the problem first, and/or to divide the problem into smaller parts and solve each part separately, as is done in the "jet ski" problem in the GCF video. The process of figuring out how to solve problems in Excel also requires the same skills needed to solve math word problems, about which see some relevant tutorials listed here.

    The first part of your paper will involve a problem chosen by you and approved via email. The problem may involve any topic that interests you, within reason. The first step is for you to find some problem relevant to any one of the following: your career goals, your current job, your major, your hobbies, or your life in general, e.g. a budget plan or diet plan. Your problem should have roughly the same (or slightly greater) level of difficulty as next week's Excel assignment, which will entail (1) a grade roster spreadsheet (including a quiz average with lowest score dropped) and (2) a spreadsheet which computes an invoice for several products sold in a store (with sales tax).

    Please choose a problem ASAP. Once you have at least tentatively chosen a problem, email me for approval, to ensure that the problem you have chosen is neither too easy nor too hard. Your email must be sent to the following email address:

    cs12-hw@nyclocal.net

    with a SUBJECT line like the following:

    HW 1 problem-solving lastname firstname

    replacing "LastName" and "FirstName" with your own actual last and first names.

    Please be sure to follow these insructions regarding the subject line of your emails, to help me keep track of your homework.

    The email is due next week, but it is strongly recommended that you send it sooner, in case your proposed problem is rejected as either too trivial or too complicated. If your problem is not too complicated but does require you to learn one or more Excel functions or features that are not taught during the first three weeks of the course, you will be referred to relevant tutorial material.

    Part 1 of the problem-solving paper itself will be due Sunday, March 1, 2020. The paper must be written using Microsoft Word, about which see this week's provided tutorial material.

  3. Scratch assignment (due before next class, February 9):
  4. Below, you are asked both to (1) go through Scratch's built-in "Getting started with Scratch" tutorial during the classroom lab session (or at home, if you missed the first class), and then (2) create an additional file, using variations on the same features covered in the tutorial, as a homework assignment. The file you will be creating during the classroom lab must NOT use sound, whereas the one you create at home WILL be required to use sound as instructed in the tutorial.

    1. Preliminary tutorial exercise:
    2. Open Scratch. (If you are doing this at home, you will need to download and install the Scratch offline editor first.  IMPORTANT NOTE:  We are still using Scratch version 2 this semester. The latest version of Scratch is Scratch 3, which came out only in January 2019. We have not yet updated our assignments and tutorial material to be compatible with Scratch version 3. Furthermore, Scratch 3 itself appears to be somewhat buggy (as new major software versions often are) and is not yet accompanied by adequate tutorial material. Therefore, do NOT use Scratch version 3, which is incompatible with Scratch version 2.)

      On the right side, near the top, is a question mark in a circle. Click the question mark to make a collection of "tips" appear, including a list of step-by-step tutorials. (If you don't see the question mark, another way to make the list of step-by-step tutorials appear is by clicking the "Tips" menu at the top left.) Then click on the first tutorial, "Getting started with Scratch."

      Follow that tutorial exactly, EXCEPT for the following: (a) If you are doing this during a classroom lab session, don't use sounds, but use another block instead, as detailed below. (b) When you are finished with your project, don't "share" the project, but save it to a file on your local machine instead.

      When you get to the "Add a Sound" step, don't add a sound when doing this assignment in the classroom lab. Instead, click "Control" and then drag the "wait 1 secs" block to fit just below "move 10 steps." Then click on the "1" in "wait 1 secs" and edit it to change it to 0.1 instead of 1 (so it will wait just a tenth of a second). Then double click on the set of two blocks that you just now put together, and see the cat move. (But you won't see the effect of the wait until you do the next step.)

      Then, in the next step, "Start a Dance," again use a "wait 1 secs" block instead of the drums. This time, edit the number in "wait 1 secs" to change it to 0.2 or 0.3 instead of 1. Then double click on the set of four blocks that you just now put together, and see the cat move. (You won't see the effect of the this second wait until you go on to the next step.)

      Complete the rest of the tutorial exactly as instructed, except for the following:

      In the "Explore!" step, if you are doing this in the classroom lab, skip the sound part, but do the animation part. (Note: the "next costume" block can be found in the "Looks" submenu under "Scripts".)

      Also, skip the very last step for now, "Share your project." Instead of "sharing" it, save your project by clicking on the "File" menu and selecting "Save As." Use whatever filename you like; you won't be submitting this first version as homework, except to send the practice email below.

      Make sure you can find the file on your machine, outside of Scratch itself, and email it to the following address:

      cs12-hw@nyclocal.net

      Give your email message the following subject line:

      Prelim Scratch LastName FirstName

      replacing "LastName" and "FirstName" with your actual last and first name.

      Then, in Scratch, go to the "File" menu again and select "New" to make this project disappear from your Scratch window. Then go to the "File" menu again and select "Open" and open the file that you just saved, to verify that you saved it correctly. Then make this first version of the project disappear again by clicking "File" and "New" again.

      Now for the part of this assignment you will be graded on.

    3. Homework assignment:
    4. Download and install the Scratch offline editor if you have not done so already.

      Create another project similar to the one above, but with three dancers instead of two. Use all the same features you used in the previous project, but use them in somewhat different ways, e.g. by varying the timing, the number of repetitions, the content of the "say" block, other effects besides just changing color, etc. Also, try using a few additional blocks to see what they do. Have fun!

      (Note: We will not grade on quality of creativity, just on whether you customized your project enough to show that you understand the basics of Scratch.)

      If you are doing this at home, don't refrain from using sounds. Use the sounds as asked for in the tutorial.

      Then go to the "File" menu, select "Save As" and save your project with a filename in the following format:

      • lastname-firstname-dance-1.sb2

      replacing "lastname" and "firstname" with your actual last and first name. (Note: ".sb2" is an extension that is automatically appended to Scratch project filenames, just as "*.xlsx" is automatically appended to Excel filenames.)

      Then email the file with the following subject line:

      HW 1 Scratch LastName FirstName

      replacing "LastName" and "FirstName" with your actual last and first name.

    If you are unable to complete the assignment at home and need help after our next class session, please bring headphones, so that you can play the sounds without disturbing other students.

  5. Excel (in-class demo only):
  6. No Excel assignment this week. However, if you missed the first class, you should look at this week's Excel tutorials and experiment with various techniques described.

  7. Powerpoint slide show, part 1 (due Sunday, February 23):
  8. Your PowerPoint presentation will be a multi-part term project. The first two parts will count as normal homework assignments, and then the final version will count 4% of your total score.

    The first part of your PowerPoint presentation will compare at least two of the paint/drawing/graphics/art tools of PowerPoint itself with the corresponding paint/drawing/graphics/art tools of Scratch.

    Scratch's paint tools will be covered in class next week.

    In the meantime, you should spend some time getting familiar with PowerPoint's drawing and graphics tools, using this week's tutorial material on PowerPoint. You should also spend some time getting familiar with PowerPoint more generally, if you're not already familiar with it. In particular, make sure you know how to create a series of PowerPoint slides, each slide containing one of the following: (1) a brief introductory sentence followed by a list of bullet points; (2) a table (very useful when summarizing comparisons); and (3) a picture with a caption, where the picture may be either one of your own drawings or an imported graphics file. Make sure you know how to create slides with all these kinds of content.

    More detailed instructions for the PowerPoint project, including submission instructions, will be provided next week.


To all information for Week 1  |  To all homework assignments



Assignment 2 (most parts due before next week's class)

  1. PowerPoint slideshow, part 1 (due Sunday, February 23, 2020)
  2. Excel assignment (due Sunday, February 16, 2020, before class)
  3. Scratch assignment (due Sunday, February 16, 2020, before class)
  4. Problem-solving paper, part 1 (due Sunday, March 1, 2020; preliminary email due ASAP THIS WEEK)

  1. PowerPoint slideshow, part 1 (due Sunday, February 23, 2020):
  2. Create a series of 5 to 10 PowerPoint slides in which you (1) compare each of at least two PowerPoint drawing/graphics tools to its nearest corresponding Scratch drawing/paint tool, and (2) very briefly compare PowerPoint as a whole, in a general way, to Scratch as a whole. (PowerPoint and Scratch are very different kinds of programs but have some capabilities in common.)

    For each type of paint/drawing tool you discuss, indicate whether PowerPoint and/or Scratch has (a) a raster (a.k.a. bitmap) version of the tool, (b) a vector version of the tool, or (c) both. Find at least two other points of comparison between PowerPoint's and Scratch's variants of the tools as well.

    Also, note that PowerPoint has both "drawing" tools and "ink" tools. What are the key differences between these two categories of tools? ("Drawing" tools can be used on a desktop or laptop with a keyboard and mouse, whereas "ink" tools are intended to be used with a touchscreen on a tablet.) Your slideshow should be primarily about "drawing" tools, NOT "ink" tools, although, if there happen to be "ink" tools similar to the particular "drawing" tools you have chosen, your slide show should briefly mention the relevant "ink" tools too, clearly distinguishing each "ink" tool from its corresponding "drawing" tool.

    At least one (and preferably more than one) of the PowerPoint drawing tools you discuss must be visibly used (as well as described and/or depicted) within your PowerPoint presentation itself. At least one of your slides must contain a drawing created using one or more of PowerPoint's drawing tools. (A very simple drawing will suffice; it doesn't need to be anything fancy.)

    (Because this course requires you to use a desktop or laptop, but not a tablet, you are NOT required to learn how to USE the "ink" tools, but only to be aware of their existence.)

    Do NOT include any animation features in Part 1 of your slideshow. You'll be asked to deal with animation later, in Part 2.

    Note that a PowerPoint slideshow is not an essay. Your slides should be designed to serve as appropriate visual aids if you were to give a brief lecture about the paint/drawing tools of PowerPoint and Scratch. Your slides should be clear but brief, with just enough information to help your hypothetical audience take good notes. Your slides should NOT spell out all the details, but should focus on the main points. It is recommended that each of your slides (except for the final credits slide) contain one of the following: (1) a brief introductory sentence followed by a list of bullet points, (2) a table (very useful when summarizing comparisons), and (3) a picture with a caption, where the picture may be either one of your own drawings or an imported graphics file. Your slideshow should include all three of these types of slides.

    Nevertheless, your slideshow MUST still have the following in common with an academic paper: The last slide must be an acknowledgments/credits page. If, on any of your slides, you copy and paste any text from the web, be sure to identify it as a quote (e.g. by putting quote marks around it) and then identify your source, in two places: (1) briefly, on the page where the quote is (e.g. as a fine-print footnote), and then (2) with a more complete bibliographic listing on the credits slide. Likewise, identify the source of any graphics you use. Not doing so is plagiarism. (See also CUNY's Academic Integrity Policy.) Your credits slide must include a small-print list of all tutorial material from which you copied either text or graphics. You must list at least two tutorials, with full bibliographic data, including their URLs if they are on the web. Furthermore, ALL graphics must be credited, even if you drew or photographed them yourself, in which case they should be credited to yourself, e.g. your credits slide may contain a sentence like: "Unless otherwise specified, all graphics are copyright ©" 2020 [YOUR NAME]. All rights reserved."

    Give your PowerPoint presentation a filename like the following:

    As always, replace "last" and "first" with your own actual first and last name. Submit the file as an attachment to an email with the following subject line:

    PowerPoint slideshow Part 1, LastName FirstName

    where "LastName" and "FirstName" must be replaced by your own last name and and first name, respectively.

    Warning:  You must follow the above submission instructions EXACTLY in order for your slideshow to be graded at all. If you do not use the correct filename, or if you do not use the correct subject line, then your slideshow will not be graded at all. These rules are necessary in order for me to keep track of your homework emails.

  3. Excel assignment (due before our next class):
  4. In this and all future Excel assignments, every required quantity that can be computed from other data in the spreadsheet must be computed by the spreadsheet itself via a formula in the appropriate cell, using cell references. Moreover, if a formula needs to appear in every cell in a column of a table, the formula must be one that you can type just once, in the first data row of the table, and then drag it down the column.

    Below are the first parts of what will be two ongoing Excel projects, a grade roster and an invoice. Because they will be continued in next week's class, you must submit both of these before next week's class in order to receive credit for them at all.

    1. Roster:  Making believe you're a teacher, create an Excel spreadsheet with columns as follows: (1) a list of names, with one column for the last name and another column for the first name; (2) five columns of quiz scores, (3) two columns of results to be computed from quiz scores, (4) two columns of in-class exam scores, and (5) 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. (See this tutorial on how to compute a quiz average with lowest score dropped. Also, note that the column heading for the quiz average with lowest score dropped will look very ugly unless you use line breaks inside the cell. For info on now to do that, see this week's Excel tutorials.)

      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. Make up a unique set of at least five names (not used by other Computer Science 12 students). The quiz scores should range between 0 to 10, while the exam scores should range between 0 and 100.

      Above the scores, there must be a row for column headings. The column headings for the quizzes should be: Quiz 1, Quiz 2, Quiz 3, Quiz 4, and Quiz 5. (Don't type all five of these quiz headings by hand. Type just two of them, then generate the rest by dragging across the row.) The column headings for the exams should be: Exam 1, Exam 2, and Final Exam.

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

      • last-first-roster-2.xlsx

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

      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.) Save the file again when finished.

    3. Invoice:  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 (one cell for each of these). The tax should be computed from the tax rate, which should be placed in a separate cell somewhere outside your main table.
    4. 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. (See this week's Excel tutorials.) Likewise use two or more merged cells for the customer's name and for 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-2.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 via email by no later than the beginning of our next class, and make sure you also have access to the files during lab on that day. The two Excel files must be sumitted as file attachements to a single email message. Please submit both at once, NOT in two separate email messages. The email message must be sent to:

    cs12-hw@nyclocal.net

    and must have a subject line like the following:

    HW 2 Excel, LastName FirstName

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

    We will use this week's Excel files again in future exercises both in lab and in homework. When a homework assignment is built upon in the very next lab session, as is the case for this one, the deadline is absolute; it cannot be submitted late. In case your email happens to be down during class, you should try to submit the files at least two hours earlier than the absolute deadline, and you must also have access to the files during class by some other means too, such as a flash drive or emailing them to yourself at a separate address.

    Warning:  You must follow the homework submission instructions EXACTLY in order for your homework to be graded at all. If you do not use the correct filenames, or if you do not use the correct subject line, or if you submit the files attached to separate email messages rather than as two attachments to just one email message, then your homework will not be graded at all. These rules are necessary in order for me to keep track of your homework emails.

  5. Scratch assignment (due before our next class):
  6. IMPORTANT NOTE:  We are still using Scratch version 2 this semester. The latest version of Scratch is Scratch 3, which came out only in January 2020. We have not yet updated our assignments and tutorial material to be compatible with Scratch version 3. Furthermore, Scratch 3 itself appears to be somewhat buggy (as new major software versions often are) and is not yet accompanied by adequate tutorial material. Therefore, do NOT use Scratch version 3, which is incompatible with Scratch version 2.

    If you already installed Scratch 3, please uninstall it as follows, to ensure that it does not interfere with Scratch 2:

    1. Open the Windows Start menu.
    2. Right click on the scratch-desktop application in the Start menu.
    3. Select uninstall.
    4. When the unistaller window opens, make sure that "scratch-desktop" is selected in the window and then click on "unistall".

    1. Preliminary practice:  Open Scratch. Go through the third of the built-in tutorials, "Animate A Name." If you are doing this in class, skip the parts involving sound for now. (Do them later, when you go through the tutorial again at home.) Unfortunately, the tutorial is not 100% clear and has some steps missing. See Notes on the "Animate A Name" tutorial for clarification of various issues. When you are finished going through the tutorial, don't "share" your work, but instead save the file. (Use whatever filename you like, since you won't be submitting this version.)
    2. Note that we've now worked with two ways of creating a new "sprite" in Scratch: (1) choosing a sprite from Scratch's built-in sprite library and (2) using Scratch's art tools to "paint" your own totally new sprite. Below is a cropped screenshot (taken by Aryeh Friedman) of the Scratch "New Sprite" icons, each for a different way of creating a new sprite.

      Scratch icons for creating a new sprite.

      In Scratch, hover your mouse cursor over each of these four icons to see all the ways that a sprite can be created.

      Now spend some time experimenting with all of Scratch's drawing tools, both "Bitmap" and "Vector" modes. Note that, in "Bitmap" mode, if you create a small image and then select it and make it a lot bigger, the image will pixelize, whereas this does not happen with images created in "Vector" mode. More generally, observe that "Bitmap" mode has some capabilities that "Vector" mode does not, and vice versa.

    3. Full name project:  Select "New" in Scratch's file menu to create a new Scratch project. Using the drawing tools, including one or both "text" tools, create two sprites, one for your first name and one for your last name. Do NOT make one sprite for each letter as you did in the "Animate A Name" tutorial. Instead, one sprite should contain all the letters of your first name and another sprite should contain all the letters of your last name. However, within each sprite, you should vary the appearance of the letters, e.g. by making them different colors and by creating some of the letters using the "text" tools but creating others using other drawing tools.
    4. Note that each script has an icon that you can use to select it and thereby bring it up on the canvas.

      Using techniques similar to what you learned in the "Animate A Name" tutorial, give the sprite containing your first name a script that, when the green flag is clicked, will make your first name move around the screen for a few seconds, ending up in its original position. Give the sprite containing your last name a script that will make it change colors when the the space bar is pressed.

      Save this project with a filename in the following format:

      • last-first-fullname.sb2

      replacing "last" and "first" with your own actual last and first names.

    5. The "Animate A Name" tutorial again (complete with sounds):  At home (NOT in the classroom) go through the "Animate A Name" tutorial again, creating a project as instructed, including the sounds this time. (Again, see Notes on the "Animate A Name" tutorial for clarification of various issues.) The name you use should be your own first or last name.
    6. When finished, do not "Share" your project, but, instead, save it with a filename in the following format:

      • last-first-animate-name.sb2

      replacing "last" and "first" with your own actual last and first names.

    Submit BOTH of the above files via email by no later than the beginning of our next class, and make sure you also have access to the files during lab on that day. The two Scratch files must be sumitted as file attachements to a single email message. Please submit both at once, NOT in two separate email messages. The email message must have a subject line like the following:

    HW 2 Scratch, LastName FirstName

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

    Warning:  You must follow the homework submission instructions EXACTLY in order for your homework to be graded at all. If you do not use the correct filenames, or if you do not use the correct subject line, or if you submit the files attached to separate email messages rather than as two attachments to just one email message, then your homework will not be graded at all. These rules are necessary in order for me to keep track of your homework emails.

  7. Problem-solving paper, part 1 (due Sunday, March 1, 2020; preliminary email due ASAP THIS WEEK):
  8. If you have not done so already, choose a problem relevant to your life or interests that can be solved using an Excel spreadsheet. If you expect to have difficulty thinking of such a problem, you may stay after class to brainstorm about it. Once you have chosen your problem, send me email about it ASAP for approval. Your email must have a SUBJECT line like "HW 1 problem-solving LastName FirstName", replacing "LastName" and "FirstName" with your own actual last and first names.

    Then, once your problem is approved, please begin work on the paper itself as soon as possible. As explained in Assignment 1, Part 1 of your paper should discuss the process of figuring out how to solve your problem.

    If possible, divide your problem into several smaller problems and figure out how to solve each sub-problem separately. If you can do this, your paper can discuss how you divided the problem and how you solved each distinct part and then put the parts together into your solution of the original problem. (A simple example of this technique can be found in the "jet ski" problem in the video on this page: Solving Real-Life Problems in Excel on the GCF Learn Free site.) If a clean division of your problem into distinct parts is not possible, or at least not easy, then figure out how to solve several similar but simpler problems (as exemplified in Excel problem-solving example: Quiz average with lowest score dropped), as a way to gain insight into your original problem. Your paper can then discuss both the similarities and the differences between your original problem and the simpler problems.

    In either case, you are likely to encounter a few pitfalls along the way. Your paper should discuss these sources of error and confusion and how you found and fixed your errors. For some hints on how to track down errors, see Double-Check Your Formulas on the GCF Learn Free site.

    If you used any tutorial material, be sure to credit your sources in the paper.

    You may also copy and paste excerpts from your spreadsheet into the paper, as illustrations. If you do that, give each such excerpt an appropriate caption in your paper, indicating its significance.

    Part 1 of your paper may be anywhere between 2 and 5 whole pages long, double-spaced. To make it easier to read, it should be organized into sections with headings, where each section is no longer than one full page but no shorter than half a page.

    Submit BOTH your paper (Part 1), as a Microsoft Word document, and the spreadsheet in which you solved your problem, as separate files, together in one email message, by no later than Sunday, March 1, 2020. Your paper must have a filename with the following format:

    replacing "last" and "first" with your own actual last and first names. The accomanying spreadsheet must have a filename with the following format:

    again replacing "last" and "first" with your own actual last and first names. Submit both files together in a single email message with a subject line like the following:

    Paper part 1, LastName FirstName

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

    Warning:  You must follow the above submission instructions EXACTLY in order for your paper to be graded at all. If you do not use the correct filenames, or if you do not use the correct subject line, or if you submit the files attached to separate email messages rather than as two attachments to just one email message, then your homework will not be graded at all. These rules are necessary in order for me to keep track of your homework emails.


To all information for Week 2  |  To all homework assignments



Assignment 3 (most parts due before next week's class)

  1. Excel assignment (due February 23)
  2. Scratch assignment (due February 23)
  3. Number base conversions practice
  4. Powerpoint presentation (due February 23)
  5. Problem-solving paper, part 1 (due March 1, but get your chosen problem approved ASAP)

  1. Excel assignment (due next week):
  2. In this and all future Excel assignments, every required quantity that can be computed from other data in the spreadsheet must be computed by the spreadsheet itself via a formula in the appropriate cell, using cell references. Moreover, if a formula needs to appear in every cell in a column of a table, the formula must be one that you can type just once, in the first (top) data row of the table, and then drag it down the column. Likewise, if a formula needs to appear in every cell in a row of a table, the formula must be one that you can type just once, in the first (leftmost) data column of the table, and then drag it rightward across the row.

    1. Continuing the grade roster assignment from last week:
    2. 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 (3) 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.

      As before, your Excel spreadsheet should have a filename having the following format:

      • last-first-roster-3.xlsx

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

    3. Continuing the invoice assignment from last week:
    4. Pretty it up. Among other things, it should now display a date if it didn't already. Use either a DATE function or the TODAY function for the date -- whichever one is more appropriate. (Only one of them is appropriate, assuming that the spreadsheet is intended to be kept as a record of a transaction that took place on a particular date.) Pretty up the spreadsheet in other ways too, such as making sure that column headings are lined up with numbers beneath, and making sure that the money amounts are numbers formatted as currency.

      Make sure also that the tax rate is in a separate cell, with a label identifying it in an adjacent cell. And make sure that everything that can be computed (such as totals) is computed automatically via formulas in the spreadsheet. Do NOT hardcode any values computed by you, the human, outside of the spreadsheet.

      As before, the invoice should have a filename with the following format:

      • last-first-invoice-3.xlsx

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

    Submit BOTH of the above files via email by no later than the beginning of our next class session, and make sure you also have access to the files during lab on that day. The two files must be submitted as file attachments to a single email message. Please submit both at once, not in separate email messages. The email message should have a subject line like the following:

    HW 3 Excel, LastName FirstName

    where "LastName" and "FirstName" must be replaced by your own last name and and first name, respectively.

    We will use this week's Excel files again in future exercises both in lab and in homework. When a homework assignment is built upon in the very next lab session, as is the case for this one, the deadline is absolute; it cannot be submitted late. In case your email happens to be down during class, you should try to submit the files at least two hours earlier than the absolute deadline, and you must also have access to the files during class by some other means too, such as a flash drive or emailing them to yourself at a separate address.

    Warning:  You must follow the homework submission instructions EXACTLY in order for your homework to be graded at all. If you do not use the correct filenames, or if you do not use the correct subject line, or if you submit the files attached to separate email messages rather than as two attachments to just one email message, then your homework will not be graded at all. These rules are necessary in order for me to keep track of your homework emails.

  3. Scratch assignment (due next week):
  4. Work through the following two animated tutorials that are a part of Scratch itself: "Birthday card" and "Make It Fly". (The tutorials are listed in a column on the right side of the Scratch window after you click "Tips" in the menu at the top left.) See Notes on the Scratch tutorials for some written clarifications to each animated tutorial, plus one or more "additional steps" that you will be required to do, after each tutorial, as part of this homework assignment.

    Save your work in files with appropriate filenames, as follows:

    where "last" and "first" must be replaced by your own actual last name and first name. Each file must contain the results of working through the named tutorial plus the "additional step" given in Notes on the Scratch tutorials.

    Then send all three files attached to ONE email with the following subject line:

    HW 3 Scratch, LastName FirstName

    where "LastName" and "FirstName" must be replaced by your own last name and and first name, respectively. Be sure to attach all three files to ONE email message. Do NOT submit the three files in three separate emails.

    Warning:  You must follow the homework submission instructions EXACTLY in order for your homework to be graded at all. If you do not use the correct filenames, or if you do not use the correct subject line, or if you submit the files attached to separate email messages rather than as three attachments to just one email message, then your homework will not be graded at all. These rules are necessary in order for me to keep track of your homework emails.

  5. Number base conversions practice:
  6. As part of your preparation for next week's quiz, you should give yourself some practice converting binary (binary 2) numbers to decimal (base 10) numbers and vice versa, and converting binary numbers to hexadecimal (base 16) numbers and vice versa.

    Begin by working through all the examples in all the tutorials listed in the section on "Binary Numbers" in Some basics of how computers work. Then choose several arbitary numbers between 0 and 256 (base 10) and convert each number from decimal to binary, and then from binary back to decimal, verifying that you get back the original number. Also practice converting each of the resulting binary numbers to hexadecimal and then back to binary.

    You are not required to submit these exercises on number base conversions. Just do them in preparation for the quiz.

  7. PowerPoint presentation (due next week);
  8. If you have not done so already, create a series of 5 to 10 PowerPoint slides in which you compare each of two PowerPoint graphics/drawing tools to its nearest corresponding Scratch tool.

    At least one (and preferably more than one) of the PowerPoint features you discuss must be visibly used (as well as described and/or depicted) within your PowerPoint presentation itself.

    Additional important instruction: In your PowerPoint presentation, if you copy and paste any text from the web, be sure to identify it as a quote and identify your source. Likewise, identify the source of any graphics you use. Not doing so is plagiarism. (See CUNY Policy on Academic Integrity - Queens College (PDF).) The last slide of your Powerpoint presentation should be an acknowledgments page, containing a list of all tutorial material from which you copied either text or graphics. You must list at least three tutorials, with full bibliographic data, including their URLs if they are on the web.

    Give your PowerPoint presentation a filename like the following:

    As always, replace "last" and "first" with your own actual first and last name. Submit the file as an attachment to an email with the following subject line:

    PowerPoint slideshow part 1, LastName FirstName

    where "LastName" and "FirstName" must be replaced by your own last name and and first name, respectively.

  9. Problem-solving paper, part 1 (due March 1, but get your chosen problem approved ASAP);
  10. For details about the paper, see last week's assignment.

    The deadline for Part 1 of the problem-solving paper is March 1. However, if the decision has not yet been fully made as to what problem you'll be solving, we need to resolve that ASAP. In that case, please stay after class to brainstorm, and then follow up with an email.


To all information for Week 3  |  To all homework assignments



Assignment 4 (most parts due before next week's class)

  1. Excel assignment (due March 1)
  2. Scratch assignment (due March 1)
  3. Problem-solving paper, part 1 (due date posponed to March 8)

  1. Excel assignment (due next week):
  2. In the problems below, you will practice having your spreadsheet do automated decision-making via the IF function.

    1. Continuing the grade roster assignment from last week:
    2. Add columns for the total score and "pass/fail." The total score will be based on the following formula: 40% final exam, plus 15% first in-class exam, plus 15% second in-class exam, plus 30% of ten times the quiz average with lowest score dropped. (The quiz score needs to be multiplied by ten to scale it up from a range of 0 to 10 to a range of 0 to 100, to be consistent with the exam scores.) The "pass/fail" column will say "pass" if the total score is at least 65, "fail" if the total score is less than 65.

      The text displayed in the "pass/fail" column must be generated by a single formula that can be written in the first row and then dragged down the column. More generally, all computed values must use formulas that can be dragged down a column or across a row, as appropriate.

      Fix all errors mentioned in the feedback you got via email from your previous version.

      As before, your Excel spreadsheet should have a filename having the following format:

      • last-first-roster-4.xlsx

    3. Continuing the invoice example from last week:
    4. Let's say the store decides to start selling some food items, if it didn't already sell food, or, conversely, let's say that the store now decides to sell some non-food items, if it was already selling food. The point being, some items are now taxable and others aren't.

      Add some rows for the new items, and add the following two columns: (1) a column indicating whether a given item is taxable (e.g., a "T" if taxable, blank otherwise), and (2) the amount of sales tax on a given item (zero if it is not taxable). The total tax should then be computed by adding up the taxes on individual items, rather than based on the subtotal of all items.

      When computing tax, you will need to refer to a single cell holding the tax rate. You'll need to use absolute addressing (with the dollar signs) to refer to that cell.

      As before, the invoice should have a filename with the following format:

      • last-first-invoice-4.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 via email by no later than the beginning of class next week, and make sure you also have access to the files during lab on that day. The two files must be sumitted as file attachements to a single email message. Please submit both at once, not in separate email messages. The email message should have a subject line like the following:

    HW 4 Excel, LastName FirstName

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

    Warning:  You must follow the homework submission instructions EXACTLY in order for your homework to be graded at all. If you do not use the correct filenames, or if you do not use the correct subject line, or if you submit the files attached to separate email messages rather than as two attachments to just one email message, then your homework will not be graded at all. These rules are necessary in order for me to keep track of your homework emails.

  3. Scratch assignment (due next week):
  4. Do the following:

    Save your work in files with appropriate filenames, as follows:

    where "last" and "first" must be replaced by your own actual last name and first name. Each file must contain the results of working through the named tutorial plus the "additional step" given in Notes on the Scratch tutorials.

    Then send all three files attached to ONE email with the following subject line:

    HW 4 Scratch, LastName FirstName

    where "LastName" and "FirstName" must be replaced by your own last name and and first name, respectively. Be sure to attach all three files to ONE email message. Do NOT submit the three files in three separate emails.

    Warning:  You must follow the homework submission instructions EXACTLY in order for your homework to be graded at all. If you do not use the correct filenames, or if you do not use the correct subject line, or if you submit the files attached to separate email messages rather than as three attachments to just one email message, then your homework will not be graded at all. These rules are necessary in order for me to keep track of your homework emails.

  5. Problem-solving paper, part 1 (due date posponed to March 8):
  6. The deadline for Part 1 of the problem-solving paper has been postponed to March 8.

    However, if the decision has not yet been fully made as to what problem you'll be solving, we need to resolve that ASAP. In that case, please stay after class to brainstorm, and then follow up with an email.


To all information for Week 4  |  To all homework assignments



Assignment 5 (most parts due before next week's class)

  1. Paper on problem-solving, part 1 (due next week, March 8)
  2. Paper on problem-solving, parts 2 and 3, preliminaries
  3. Excel assignment (due next week, March 8)
  4. Scratch assignment (due next week, March 8)

  1. Paper on problem-solving, part 1 (due next week)
  2. Part 1 of your paper on problem-solving is due next week. See the instructions posted for Week 2.

  3. Paper on problem-solving, parts 2 and 3, preliminaries
  4. This week's Excel problems are much more complicated than last week's problems. Chances are, developing the needed formulas and testing them will take quite a bit of work. You will need to break each problem down into simpler parts and gradually work your way up to the complete solution.

    Take detailed notes on your personal process of figuring out how to solve each problem. Then, having figured it out, write -- in your own words -- a step-by-step tutorial on how to figure out how to solve problems of this kind, using this week's Excel problems as examples. Next week, after you've written such a tutorial for each of this week's Excel problems, these tutorials will become parts 2 and 3 of your paper on solving problems in Excel. Draft 2, containing parts 1, 2, and 3, will be due three weeks from now. It is strongly recommended that you get at least a good start on writing the tutorials this week, while this week's Excel problems -- and the discussion of them in class -- are still fresh on your mind.

    To help you think in-depth about problem-solving, review the Week 1 tutorials on Excel and solving math word problems, and then look also at this week's tutorials on Excel and problem-solving in programming. (Although Excel is not a programming language per se, Excel formulas do have a lot in common with programming.)

    When reading each of the Excel tutorials, look first at the example problems and try to figure out the correct formulas yourself before reading the solution in the tutorial.

    Not all the techniques described in these tutorials will necessarily work well for you, but you should experiment with a variety of problem-solving techiques. If you learn any valuable techniques from any of these tutorials, try to keep track of what you learned from where, so you can credit your sources in your paper.

  5. Excel assignment (due next week):
    1. Continuing the version of the invoice assignment we did TWO weeks ago (Assignment 3, NOT Assignment 4), for a store which sells only taxable items (thus, we do NOT need to use an IF function to compute tax as we did last week):
    2. Between the row containing the cell for the original subtotal and the row containing the cell for for the tax, add two more rows containing cells for (1) a discount (to be based on the subtotal) and (2) the discounted subtotal (i.e. the subtotal minus the discount).

      The discount is to be computed as follows: (1) No discount for the first $100. (2) For subtotals above $100, five percent of the amount that the subtotal exceeds $100. (3) For subtotals above $500, the afore-mentioned discount plus an additional five percent of the amount that the subtotal exceeds $500. The discount should be applied to the subtotal of all products (not the per-product total price).

      The discount must be computed using a single formula that will work correctly regardless of how big the subtotal is. All decision-making about the discount, based on the size of the subtotal, must be done automatically by the formula (using the IF function), NOT by you, the human user.

      You should have separate cells at the bottom for (1) the original subtotal, (2) the discount, (3) the discounted subtotal (i.e. the original subtotal with discount subtracted), (4) the sales tax, and (5) the total (which will be the sales tax added to the discounted subtotal, not the original subtotal).

      It is recommended that you make a first draft of this spreadsheet using a simpler discount formula than the one described above. After you have successfully tried it with a simpler formula, experiment with more complex formulas, gradually building up to the formula described above.

      As before, the invoice should have a filename with the following format:

      • last-first-invoice-5.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.

    3. Create a spreadsheet listing at least 10 sales people who work for some particular company, to compute their rank as employees. The spreadsheet should have columns for the salespersons' names, the date that they were hired, the number of years (accounting years) they have worked for the company (computed from the date hired), the dollar amount of sales by each sales person during the past month, and the sales person's rank. The rank (3 being the highest, 1 being the lowest, is defined as follows: Sales people have rank 3 if they have worked for the company for at least five years and sold at least $20,000 worth of goods during the past month. They have rank 2 if they have either worked for the company for at least five years or sold at least $20,000 during the past month. They have rank 1 if neither of the above is true. Format the spreadsheet as a table.
    4. In a separate cell outside the table, at or near the top of the page, put a date that will be used in computing the number of years an employee has worked for the company. consider carefully what function (e.g. DATE or TODAY) is most appropriate to use in generating that date.

      The computation of rank is complicated. Try to break the computation of rank into simpler parts before you put together the entire formula for the rank.

      This spreadsheet should have a filename with the following format:

      • last-first-salesforce-5.xlsx

      We will use this file again in future exercises both in lab and in homework.

    Submit BOTH of the above files via email by no later than the beginning of next week's class, and make sure you also have access to the files during lab on that day. The two files must be sumitted as file attachements to a single email message. Please submit both at once, not in separate email messages. The email message should have a subject line like the following:

    HW 5 Excel, LastName FirstName

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

    Warning:  You must follow the homework submission instructions EXACTLY in order for your homework to be graded at all. If you do not use the correct filenames, or if you do not use the correct subject line, or if you submit the files attached to separate email messages rather than as three attachments to just one email message, then your homework will not be graded at all. These rules are necessary in order for me to keep track of your homework emails.

  6. Scratch assignment (due next week):
  7. Do the following three tutorials that are a part of Scratch itself: "Hide-and-Seek Game", "Create A Story", and "Create a Pong Game". (The tutorials are listed in a column on the right side of the Scratch window after you click "Tips" in the menu at the top left.) See Notes on the Scratch tutorials for some clarifications to each tutorial, plus an "additional step" that you will be required to do, after each tutorial, as part of this homework assignment.

    Save your work in files with appropriate filenames, as follows:

    where "last" and "first" must be replaced by your own actual last name and first name. Each file must contain the results of working through the named tutorial plus the "additional step" given in Notes on the Scratch tutorials.

    Before you submit your homework, please delete any unused sprites, variables, and scripts.

    Then send all three files attached to ONE email with the following subject line:

    HW 5 Scratch, LastName FirstName

    where "LastName" and "FirstName" must be replaced by your own last name and and first name, respectively. Be sure to attach all three files to ONE email message. Do NOT submit the three files in three separate emails.

    Warning:  You must follow the homework submission instructions EXACTLY in order for your homework to be graded at all. If you do not use the correct filenames, or if you do not use the correct subject line, or if you submit the files attached to separate email messages rather than as three attachments to just one email message, then your homework will not be graded at all. These rules are necessary in order for me to keep track of your homework emails.


To all information for Week 5  |  To all homework assignments



Assignment 6 (most parts due before next week's class)

  1. Access assignment (due next week, March 15, 2020)
  2. Scratch assignment (due next week, March 15, 2020)
  3. Paper on problem-solving, Draft 2 (due March 22, 2020)

  1. Access assignment (due next week):
  2. Following the detailed instructions below, create a single-table database which will also have a form, a query, and a report. The table will list employees. The query and the report will list "senior employees," a subset of the employees in the original table.

    1. Creating a table in Access:
    2. Open Microsoft Access 2013 or 2016. Double-click "Blank desktop database," which will be one of the large icons on the right. (These icons represent commonly-used types of databases.) You will be shown a blank new table.

      First let's give the table an appropriate name. Click the "File" tab, then click "Save." In the "Save As" dialog box that pops up, change "Table1" to "Employees."

      Let's now create ("design") appropriate fields (columns) for this table. At the top of the main panel is a tab marked "Employees". Right click that tab and select "Design View." You'll now see a new table whose purpose is to define fields for the original table. To go back to the original table, click the "Employees" tab again and select "Datasheet view." Then go back to "Design View" again, and toggle back and forth between the two views until you see how they relate to each other.

      Notice that one field has already been defined for us: A field named "ID," whose data type is "AutoNumber." This field will automatically generate unique ID's for all the employees in our table.

      In "Design View," let's now define the following additional fields, as explained below:

      • A field named "Last name," of data type "Short Text"
      • A field named "First name," also of data type "Short Text"
      • A field named "Date of birth," of data type "Date/Time"
      • A field named "Salary," of data type "Currency."

      To define each of the above four new fields, type its name in the "Field Name" column of the "Design View" table, and select its data type from a drop down list.

      Then right-click the "Employees" tab at the top of the table and select "Save." Then right-click the "Employees" tab again and select "Datasheet View."

      Now enter data for 10 employees. Make up their names, birth dates, and salaries. Note that the ID is automatically filled in for you. Choose birth dates such that about half the employees are over 30 years old and the other half are under 30. For both age ranges, choose salaries such that some are earning more than $50,000 (per year) and others are earning less than $50,000.

      Then right-click the "Employees" tab and click "Save."

    3. Creating a form:
    4. Let's now create a form which will enable more data to be entered into the table in a more user-friendly fashion, one new employee at a time.

      At the top of the window, select the "Create" tab, Then click "form." At first, the form will display the data for your first employee. So that you can use it to enter data for new employees, hover the mouse over the little arrow icons at the bottom of the form and click the one that says "New (blank) record" when you hover over it You'll then see the fields all go blank except for "(New)" in the ID field..

      The form has the name "Employees," same as the table. Let's change its name to "Employee" (singular), to distinguish it from the table. To do this, right click the "Employees" tab for the form (not the table) and select "Save" and then, in the "Save As" dialog box that pops up, delete the final 's' from the word "Employees."

      The form still has a heading at the top, just below the tabs, that says "Employees." To change that, simply double-click on that heading and delete the final 's' from the word "Employees," and then press the [Enter] key.

      Right-click on the "Employee" tab again and observe the available views: "Form view," "Layout view," and "Design view." The "Form view" is intended for entering data, whereas the other two views are intended for changing the appearance of the form. By default, when you create a new form, you are initially in "Layout view."

      Select "Form view" so you can enter data. Then, if you have not done so already, hover the mouse over the little arrow icons at the bottom of the form and click the one that says "New (blank) record" when you hover over it.

      The cursor will now be on the "(New)" in the ID field. Since that field is auto-generated, you can't put a value there yourself. So, just press Enter to move your cursor to "Last Name".

      Enter data for a few new employees.

      Then right-click the "Employee" tab and select "Save".

      Then click the "Employees" tab to go back to the "Employees" table. At first, you won't see the new employees. One way to make them appear is to close the "Employees" table (by right-clicking the "Employees" table and selecting "close") and then re-open the table (by double-clicking "Employees" in the left panel).

    5. Creating a query:
    6. Let's now use our table to look up some information. Suppose we want a listing of all the employees over 30 years old who are earning at least $50,000. Given how short our table is, it's not too difficult to do this by hand. But, if we had a lot more employees, looking up this information by hand could be quite tedious. So, let's use a query to automate our search.

      At the top, click the "Create" tab, then select "Query Wizard." Select "simple query wizard" and click "OK."

      First, we are asked to select which fields will appear in our query result. Select all the fields. Then click "Next." Then select "Detail" and click "Next."

      In the next dialog bax, you are asked what name you want for the query, with default name "Employees query." Change the name to "Senior employees." Then select "Modify the Query Design" and click "Finish.".

      At the bottom, in the "Criteria" row, in the cell in the "Date of birth" column, enter "<" followed by a date 30 years ago. Also in the "Criteria" row, in the cell in the "Salary column, enter ">=" followed by the number 50000. (Do not use a dollar sign, and do not use a comma.)

      Then right-click the "Senior employees" tab and select "Datasheet view." The quary results should then be generated and displayed. You should see only those employees who fit your criterial If you get an error message, see Examples of query criteria on the Microsoft site.

      Now save the query. Right click the "Senior employees" tab and click "Save."

    7. Creating a report:
    8. Let's now create a report to display our query results in a customizable fashion.

      At the top, click the "Create" tab, then select "Report." Then play around with various features to see how they change the appearance of your report.

      Then save your report.

    9. Saving your database:
    10. After you've saved and then closed all the individual objects in your database, save your database itself. Click the "File" tab, then select "Save As," then select "Save Database As" and give your database a filename like the following:

      last-first-employees.accdb

      replacing "last" and "first" with your own last and first name.

    Submit the above file via email with a subject line of

    HW 6 Access, LastName FirstName

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

    Warning:  You must follow the homework submission instructions EXACTLY in order for your homework to be graded at all. If you do not use the correct filenames, or if you do not use the correct subject line, or if you submit the files attached to separate email messages rather than as three attachments to just one email message, then your homework will not be graded at all. These rules are necessary in order for me to keep track of your homework emails.

  3. Scratch assignment (due next week):
  4. Review the Scratch tutorial titled "Create A Pong Game". See Create A Pong Game for clarifications on some issues.

    Then do the following additional tutorials:

    Save your work in files with appropriate filenames, as follows:

    where "last" and "first" must be replaced by your own actual last name and first name. Each file must contain the results of working through the named tutorial.

    Then send both files attached to ONE email with the following subject line:

    HW 6 Scratch, LastName FirstName

    where "LastName" and "FirstName" must be replaced by your own last name and and first name, respectively. Be sure to attach all three files to ONE email message. Do NOT submit the three files in three separate emails.

    Warning:  You must follow the homework submission instructions EXACTLY in order for your homework to be graded at all. If you do not use the correct filenames, or if you do not use the correct subject line, or if you submit the files attached to separate email messages rather than as three attachments to just one email message, then your homework will not be graded at all. These rules are necessary in order for me to keep track of your homework emails.

  5. Paper on problem-solving, Draft 2 (due March 22, 2020)
  6. Write parts 2 and 3 of your paper, describing how to think through the process of solving various kinds of Excel problems, using last week's Excel problems as examples. The example for Part 2 should be the invoice with discount, and the example for Part 3 should be the sales force ranking problem. Among other things, show possible ways to break each problem down into simpler parts and gradually work your way up to the complete solution.

    Draft 2 of the paper will have three parts, each devoted to a distinct problem. Note that parts 2 and 3 do NOT need to be in any way related to the problem you solved in part 1. Rather they should be presented as separate, mutually independent examples of how to think through the process of solving problems in Excel.

    To get started thinking in-depth about problem-solving, review your notes on last week's Excel lectures. Also review the tutorials on solving math word problems and last week's tutorilas on Excel and programming. When reading each of the Excel tutorials, look first at the example problems and try to figure out the correct formulas yourself before reading the solution in the tutorial.

    You will be expected to use Microsoft Word to write your paper. Some tutorials on Microsoft Word are provided.

    Parts 2 and 3 of the problem-solving paper should each be between 1 and 2 pages long. (Together these two parts should be between 2 and 4 pages long.) They must be appended to Part 1, which should be edited in accordance with any feedback you've received on it. The three parts must each have section headings.

    Your entire paper so far (parts 1, 2, and 3) should be divided into 3 main sections, each with sub-sections. The three main sections should be devoted to (1) the problem you solved in part 1, (2) the invoice with discount, and (3) the sales force ranking problem. Each of the three main sections should be divided into a few sub-sections, each devoted to some aspect of the problem or a step in solving it.

    The paper, which will be due two weeks from now, must be a Microsoft Word document with a filename with the following format:

    where "last" and "first" should be replaced by your own last and first names. Include also the spreadsheet for part 1, which must have a filename with the following format:

    again replacing "last" and "first" with your own actual last and first names. Submit both files together in a single email message with a subject line like the following:

    HW 7 Paper draft 2, LastName FirstName

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


To all information for Week 6  |  To all homework assignments



Assignment 7 (most parts due before next week's class)

  1. Excel assignment (due next week, April 5)
  2. Scratch assignment (due next week, April 5)
  3. Non-Web Internet file transfer
  4. Paper on problem-solving (Part 2 due next week, April 5)

Important announcements: (1) Some clarifying additional info has been added to the "salesforce" part of the Excel assignment, below. (2) Due to the "recalibration period," the due date for Assignment 7 has been postponed from March 29 to April 5. (3) There will not be a regular class session on Sunday, March 29, but there will be an optional tutoring chat during the regular class time on that day. (Those who were unable to participate in the regular class session on March 22 are especially urged to participate in the optional March 29 chat if possible.)

  1. Excel assignment (due next week):
    1. Re-do, with SUMIF, the invoice problem from week 4, with taxable and non-taxable items -- NOT the invoice example from week 5, with discounts. Your invoice spreadsheet for this week must have both taxable and non-taxable items, but should NOT have discounts.
    2. The use of SUMIF will allow you to simplify your spreadsheet, eliminating the column which computes per-product tax.

      As before, the invoice must have a filename with the following format:

      • last-first-invoice-7.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.

    3. Table of sales people (continued from two weeks ago):
    4. Continuing the "sales force" exercise from week 5: A little further down on the worksheet, create a new separate table containing results computed from the original table. The new second table should contain rows for each rank of employee (one row per rank), with columns for (1) the rank, (2) the total number of employees of a given rank, (3) the total amount of sales by all employees of a given rank, and (4) the average sales by employees of a given rank. (Here is a PDF document showing a blank version of the new table..)

      In each column of computed results, the formula should be draggable, i.e. it should be possible to write the formula just once in the first row and then drag it down the column to generate correct formulas for subsequent rows. To that end, it is recommended that (1) the rank be referred to by a cell reference to the rank in the first column, and (2) all references to ranges in the other table be absolute cell references (with the dollar signs) so that they don't change as the formula is dragged.

      (If your formulas don't seem to be working, one thing to check is the data type you used in your formula for the rank in the first table (in the HW 5 version of the salesforce spreadsheet). Note that the numeric values 1, 2, and 3 are not seen by Excel as being equal to the text values "1", "2", and "3" respectively.)

      This spreadsheet should have a filename with the following format:

      • last-first-salesforce-7.xlsx

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

    Submit both of the above files together via a single email message with a subject line like the following:

    HW 7 Excel, LastName FirstName

    where "LastName" and "FirstName" should be replaced by your own last name and and first name, respectively. We will use these files again in future exercises both in lab and in homework.

    The Excel assignment must be submitted, via email, by no later than the beginning of our next class session. If you have any trouble submitting them, come early to lab and ask me to help you. (Have access to your files in lab, of course.)

  2. Scratch assignment:
  3. Do the following tutorial:  Scratch interactive animation example

    Save your work in a file named last-first-curtains.sb2 ,  where "last" and "first" must be replaced by your own actual last name and first name. Send this file in an email with the following subject line:

    HW 7 Scratch, LastName FirstName

    where "LastName" and "FirstName" must be replaced by your own last name and and first name, respectively.

  4. Non-Web Internet file transfer:
  5. Make sure you can access your account on the CS 12 Unix machine as instructed in this week's tutorials on non-Web Internet file transfer. Try this both in class and on your machine at home. You will need to download and install the necessary software on your machine at home.

  6. Paper on problem-solving (Draft 2 due next week)
  7. See the descriptions of draft 2 of the problem-solving paper in Week 5 and Week 6.


To all information for Week 7  |  To all homework assignments



Assignment 8 (most parts due before next week's class)

  1. Necessary (and tricky!) preliminaries to the HTML assignment
  2. HTML assignment (due before our next class, April 12)
  3. Blog (due before our next class, April 12)
  4. Excel practice
  5. Excel extra-credit assignments (due Sunday, April 26)
  6. Paper on problem-solving, draft 3 (due Sunday, April 19)
  7. Powerpoint slide show, draft 2 (due Sunday, April 26)
  1. Necessary (and tricky!) preliminaries to the HTML assignment

    For your HTML assignments, you must use a plain text editor, NOT an "HTML editor" program. HTML editors (which typically hide the actual HTML) tend to add a lot of extra junk that you don't need. In your HTML assignments, you will lose points for unnecessary irrelevant junk.

    However, before you can begin creating HTML files in a plain text editor, there are some possibly tricky preliminaries you must deal with first. In the HTML-related tutorials area, see first the section on either Editing HTML files using TextEdit on a Mac or Editing HTML files using Windows Notepad, depending on which kind of machine you are using.

    On a Windows machine, another crucial preliminary issue is that you must make filename extensions visible. (Otherwise, Windows Notepad can create  *.txt  files only, not  *.html  files.) On Windows, filename extensions are invisible by default. For info on how to make them visible, see the tutorials in the sections on Filename extensions in Windows 10 and Windows 7.

    Do not attempt to create an HTML file without first taking care of the matters above.

    After you've mastered the above, use your text editor (e.g. Notepad or TextEdit) to create a blank HTML file with the filename  index.html  (exactly that filename and no other), and copy and paste into it the following text:

    <!DOCTYPE html>
    <html>
       <head>
          <title>Hi from tab!</title>
       </head>

       <body>
          <p>Hello from the main window of your browser!</p>
       </body>
    </html>

    Then save the file in your text editor.

    Then open the file in a web browser. (In Windows, right click on the filename and select a web browser other than (Microsoft) Internet Explorer or Microsoft Edge, which do not handle local files well.) You should see "Hello from the main window of your browser!" at the top of the main window. You should see "Hi from tab!" on the tab at the top.

    If you are not able to accomplish the above preliminaries, ask for help ASAP.

    Observe that the entire HTML file, except for the first line, has a structure like this:  <tag>content</tag> .  The tags are items enclosed in  <  and  > .  The tags we've seen so far are intended to be used in matching pairs, such as:

    • <html>  ...  </html>
    • <head>  ...  </head>
    • <title>  ...  </title>
    • <body>  ...  </body>
    • <p>  ...  </p>

    A pair of tags, together with whatever stuff is in between, is known as an HTML element. The tag at the beginning of an element is called the opening tag, and the tag at the end is called the closing tag. Some elements may contain other elements, e.g. the  html  element contains the  head  and  body  elements. The  head  element contains the  title  element, which contains text to be displained in the browser tab -- and would also be the title listed by search engines such as Google, if they were aware of your page. (The  head  element may also contain miscellaneous other elements that are beyond the scope of this course.) The  body  element contains everything that appears in the main window of your browser, and may contain various elements that specify the structure of the document, such as paragraphs.

    Note:  Not all HTML tags are intended to be paired or to enclose other stuff. But the specific tags we've discussed above are indeed intended to be paired.

  2. HTML assignment (due before our next class):

    A) After having created  index.html  as instructed above, modify the file in your text editor as follows:

    1. Replace  "Hi from tab!"  with your first and last name, taking care not to disturb the  <title>  and  </title>  tags.

      Save the modified file in your text editor, then reload the file in your web browser. You should now see your name on the tab.

    2. Then, in between the  <body>  and  </body>  tags, replace:

            <p>Hello from the main window of your browser!</p>

      with:

            <h1>FirstName LastName's simple website</h1>

            <p>This is a website I'm creating for Computer Science 12.</p>

      replacing  FirstName LastName  with your own actual first and last name.

      Save the file in your text editor, then reload it in your web browser to see how it looks. The first line in your main browser window should be an enlarged and bolded heading. The second line should be a normal paragraph.

      Note that the  <p>  and  </p>  tags enclose a paragraph, whereas the  <h1>  and  </h1>  enclose a main heading.

      Now try replacing  <h1>  and  </h1>  with  <h2>  and  </h2>  and see what happens.

    3. You will now be shown how to create a link in HTML. In your text editor, modify the following paragraph:

            <p>This is a website I'm creating for Computer Science 12.</p>

      so that "Computer Science 12" becomes a link to this Computer Science 12 website. Accomplish this as follows:

            <p>This is a website I'm creating for <a href="http://cs12.dnixon.nyclocal.net/">Computer Science 12</a>.</p>

      Below, the items you need to add, in order to accomplish the above, are shown in bold:

            <p>This is a website I'm creating for <a href="http://cs12.dnixon.nyclocal.net/">Computer Science 12</a>.</p>

      Note that a link is created using an  a  element. Note also that the opening  a  tag consists of more than just  <a>  -- it also contains what is known as an HTML attribute, or  name="value"  pair, where the name is  href and the value is a URL (web address).

      Be sure not to forget the closing  </a>  tag, which the  a  element also requires.

      Save the file in your text editor, then reload it in your web browser. Make sure the link works.

    B) Download the ZIP file HTML-demo.zip. Open it (by double-clicking on it, if you are using Windows) and copy the file  demo.html  into the same folder where your file  index.html  is. Then look at your copy of  demo.html  in both your text editor and your web browser to see what various HTML tags do. In particular, note that  demo.html  contains a link to  index.html .  If  demo.html  and  index.html  are together in the same folder, then the link to  index.html  in  demo.html  should work. Click on it to make sure it does work. Look at other tags/elements too, to see what they do.

    C) Using both  index.html  and  demo.html  as models, create a new HTML file with the filename  aboutme.html  which does the following:

    1. Both the browser tab and the main heading within the browser window should say:

      About FirstName LastName
      

      replacing "FirstName LastName" with your own actual first and last name.

    2. The page should contain two or three paragraphs about your life and/or your interests, possibly including your career goal and/or your hobbies. Include links to at least two relevant websites that are of interest to you. (Your inclusion of those links will count toward a significant part of your grade for the assignment.)

    Save  aboutme.html  in your text editor and open it in a web browser. Make sure the links work.

    D) Give both  index.html  and  aboutme.html  links to each other, as follows:   In  index.html ,  add the following paragraph:

          <p>Here is a page <a href="aboutme.html">about me</a>.</p>

    In  aboutme.html ,  add the following paragraph near the bottom, just above the closing  </body>  tag:

          <p>Back to <a href="index.html">my main page</a>.</p>

    Save both files in your text editor. Then open a web browser to make sure the links work. Note: the links will work only if  aboutme.html  and  index.html  are in the same folder. In your web browser, you should be able to go back and forth between the two files by clicking on the links repeatedly.

    E) When you are finished, create a new ZIP file (a.k.a. a "compressed folder") with a filename like the following (replacing "last" and "first" with your actual last and first names, as usual):

    • last-first-website-8.zip

    (To create a new empty ZIP file on a Windows machine, right-click in the empty space in a folder window, then select "New," then select "Compressed (Zipped) folder". It's actually a file, not a folder, but it will look like and appear to behave like a folder in Windows. Then rename it to give it the required filename.)

    Then copy both  index.html and  aboutme.html into the ZIP file. Your HTML homework must be submitted as a single ZIP file, NOT in the form of separate HTML files attached directly to an email message.

    Submit the ZIP file via email by no later than the beginning of next week's class, and make sure you also have access to the files during lab on that day. The ZIP file must contain both of your HTML files. The email message should have a subject line like the following:

    HW 8 HTML, LastName FirstName

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

     

  3. Blog (due before our next class):

    Before you create a blog, consider what general topic you would like to be the main focus of your blog. You may use any topic that is of interest to you, within reason, and within legal limits. (No copyright violations, no threats or other harassing content, etc.)

    You will NOT be asked to research your topic. The sole purpose of this exercise will be to give you some practice creating and posting to a blog. So, just pick a topic that you happen to be knowledgeable about, and about which you can easily write at least five or six sentences without having to think very hard. (Still, don't plagiarize. Do NOT, in this or any other exercise in this course, just copy and paste text from somewhere without giving credit.)

    Then create a blog on Wordpress.com. For details, see the tutorial Getting started on Wordpress. You may use either your real name or a psuedonym. As instructed in the above-linked tutorial, write two blog posts briefly introducing yourself and your blog's main topic.

    When you are finished with the exercise described in the tutorial, copy and paste the URL of your blog  (https://sitename.wordpress.com/  - replacing "sitename" with your actual Wordpress sitename) into an email. (Do not submit a file.) Your email must have a subject line with the following format:

    HW 8 Blog, LastName FirstName

    replacing "LastName" and "FirstName" with your own actual last and first names.

    Be sure to keep track of your username and password. You'll be asked to add more to your blog later in the semester.

  4. Excel practice:

    You will not be asked to hand in an Excel assignment next week. However, be sure to give yourself plenty of practice using the SMALL and LARGE functions and the GoalSeek tool, to prepare for next week's quiz.

  5. Excel extra-credit assignments (due three weeks from now):

    There are two extra-credit assignments that can be done in the next two weeks:

    1. Excel Solver tool:

      Work through the examples in the tutorials on the Excel solver tool included in this weeks Excel tutorials. Create spreadsheets similar to the ones in the tutorials, but using different sets of data, and use the Solver tool as instructed.

      Also write a brief paper (1 to 3 pages) explaining how you used the Solver tool. The paper does not need to go into as much detail as was asked for in the problem-solving paper, but should explain the problems well enough to show that you understand both the problems themselves and how to use the Solver tool to solve them. The paper must contain bibliographic data on whatever tutorials you used, and must contain citations for any quotes or graphics from the tutorials.

      Put at least three different (the more different from each other, the better) spreadsheet examples on different sheets within one Excel file, which must have the following filename: last-first-solver-EC.xlsx ,  where "last" and "first" must be replaced by your own actual last name and first name. The accompanying brief paper must similarly have the filename format last-first-solver-EC.docx .  Send these files together in an email with the following subject line:

      HW 9 Excel Solver EC, LastName FirstName

      where "LastName" and "FirstName" must be replaced by your own last name and and first name, respectively.

    2. Excel Array formulas

      Work through the examples in the tutorials on Excel array formulas included in this weeks Excel tutorials. Create spreadsheets similar to the ones in the tutorials, but using different sets of data, and use the array formulas as instructed.

      Put at least four different (the more different from each other, the better) spreadsheet examples on different sheets within one Excel file, which must have the following filename: last-first-array-EX.xlsx ,  where "last" and "first" must be replaced by your own actual last name and first name.

      Also write a brief paper (1 to 3 pages) explaining your examples and your use of array formulas therein. The paper does not need to go into as much detail as was asked for in the problem-solving paper, but should explain your formulas well enough to show that you understand them. The paper must contain bibliographic data on whatever tutorials you used, and must contain citations for any quotes or graphics from the tutorials. The paper must have the following filename: last-first-array-EX.docx ,  where "last" and "first" must be replaced by your own actual last name and first name.

      Send these files in an email with the following subject line:

      HW 9 Excel Array EC, LastName FirstName

      where "LastName" and "FirstName" must be replaced by your own last name and and first name, respectively.

  6. Paper on problem-solving (Draft 3 due two weeks from now)

    By now you should have written three sections of the paper, one for the problem you chose at the beginning of the semester, and one for each of the two Excel problems assigned in Week 5.

    Draft 3 of the paper must contain all of the above plus a new fourth section, discussing problem-solving techniques in general, of which your approaches to the three Excel problems are examples. Refer to the tutorials provided in Week 5, and be sure to cite them as appropriate. Put this new section at either the beginning or end of your paper, whichever flows more naturally. The new section should be at least one and a half pages long and no more than three pages long. (It may be double-spaced.)

    The paper must be a Microsoft Word document with a filename with the following format:

    • last-first-prob-solve-paper-3.docx

    where "last" and "first" should be replaced by your own last and first name. Also, together with the paper, re-submit the spreadsheet (or a corrected version thereof) that was the focus of draft 1 of your paper. Give the spreadsheet a filename like the following:

    • last-first-prob-solve-spreadsheet-1.xlsx

    again replacing "last" and "first" with your own actual last and first names. Submit both files attached to a single email message with a subject line like the following:

    HW 9 Paper Draft 3, LastName FirstName

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

  7. Powerpoint slide show, draft 2 (due due three weeks from now):

    Using this week's tutorials on PowerPoint, spend some time playing with PowerPoint's animation and transition features.

    Note that PowerPoint animations are vastly different from Scratch animations, in both their purposes and capabilities.

    Add a second part to your PowerPoint slide show comparing and contrasting the capabilities of PowerPoint animations with the capabilities of Scratch animations. The new part should be 5 to 10 slides long. Your slideshow must include demo examples of at least two of PowerPoint's animation capabilities, but must NOT contain a video file, because the latter would likely be too big to submit via email.

    Your slideshow must also contain part 1, with corrections made in view of whatever feedback you received on part 1..

    Submission instructions will be provided later.


To all information for Week 8  |  To all homework assignments



Assignment 9 (most parts due before next week's class)

  1. Access assignment (due next week, April 19)
  2. HTML assignment (due next week, April 19)
  3. Blog assignment (due next week, April 19)
  4. Reminder: Paper on problem-solving, draft 3 (due Sunday, April 19)
  5. Reminder: Powerpoint slide show, draft 2 (due Sunday, April 26)
  6. Reminder: Excel extra-credit assignments (due Sunday, April 26)
  7. Extra-credit project proposals
  1. Access assignment (due next week)

    [For Spring 2020, this assignment is optional. It turns out that Access is not available for the Mac, and, of course, if you happen to have a Mac but not a Windows PC, you can no longer access (pun intended) the Windows PC's on campus. It is still recommended that you do this assignment if you can, in which case you will receive a small amount of extra credit for it.]

    In the previous Access exercise, we created a single-table database. Let's now create a multi-table database that could be used as a class roster by an elementary school teacher. As detailed in the steps below, the database will have three tables: "Students," "Subjects," and "Grades," where the "Grades" table will have a primary key consisting of two fields, which will be foreign keys referencing the other two tables. Create the three tables as follows:

    As before, begin by opening Access and selecting "Blank desktop database." Then:

    • A) "Students" table:

      1. To begin creating the "Students" table, click "Create." Then right-click the "Table1" tab at the top of the main panel, and select "Save."
      2. In the "Save As" dialog box that pops up, change "Table1" to "Students".
      3. After saving the table, right-click the "Students" tab and select "Design View." (ALWAYS go to "Design View" and define your fields before you start entering data!)
      4. Observe that the table automatically has an "ID" field whose data type is "AutoNumber" (meaning it will automatically assign unique numbers). Do NOT do anything to this field. Leave the ID field alone, allowing it to automatically assign student ID's Observe the little key icon next to "ID" in the "Design View," meaning that the ID field will be the primary key of the record.
      5. Add three new fields for "Last Name," "First Name," and "Phone," all of data type "Short Text," which is the default data type. (You should now have a total of four fields including the ID.)
      6. Save the design (by right-clicking the "Students" tab and clicking "Save")
      7. Right-click the "Students" tab and select "Datasheet view," so you can enter data.
      8. Enter data for five students.
      9. Save the table (by right-clicking the "Students" tab and clicking "Save") and then close it (by right-clicking the "Students" tab and clicking "Close")
    • B) "Subjects" table:

      Then create the "Subjects" table. At the top, click "Create," then click "Table." Give the new table the name "Subjects" by saving it with that name. As before, define the fields in "Design View" before you start entering data in "Datashheet View." Also as before, leave the "ID" field alone, so you can make use of the automatically generated ID numbers,. In "Design View", add two new fields, one for "Subject," and one for "Notes," both of data type "Short Text." Again observe the little key icon indicating that the "ID" field is the primary key. In the "Datasheet View," add records for at least three of the subjects typically taught in elementary school, such as "reading," "math," and "science." (You may want to stretch out the width of the "Notes" column in the datasheet view; this can be done similarly to the way it's done in Excel.) When finished, save this table and close it.

    • C) "Grades" table:

      The "Grades" table will be very different from the other two. As detailed in the steps below, it will have fields for "Student," "Subject," and "Grade," where "Student" and "Subject" will each be foreign keys referring to the other two tables. Proceed as follows:

      1. At the top, click "Create," then click "Table." Give the new table the name "Grades" by saving it with that name.

      2. The "Grades" table should not have an auto-generated ID field. Get rid of this field as follows: In the Design View, right-click on the name of the "ID" field and select "Primary Key" so that is no longer the primary key. (See the key icon to the left of the name disappear.) Then right-click to the left of the field name and select "Delete Rows." (You needed to make it not be the primary key anymore before you could delete it; Access will not allow you to delete a field that is a primary key.)

      3. While still in Design Vew, create the "Student" field. Give it the name "Student" and then, for its data type, select "Lookup Wizard;"; then select "I want the lookup field to get the values from another table or query"; then click "Next"; then select "Tables: Students"; then click "Next"; then select "ID," "Last name," and "First name"; then click "Next"; then select a sort order by (1) last name and (2) first name, both in ascending order; then click "Next"; then shrink the column widths to be as narrow as possible and still be readable; then click "Next"; then check "Enable data integrity"; then click "Finish"; then click "Yes" to save the table.

      4. Similarly, create a field with the name "Subject" and then, for its data type, select "Lookup Wizard;"; then select "I want the lookup field to get the values from another table or query"; then click "Next"; then select "Tables: Subjects"; then click "Next"; then select "ID" and "Subject," then click "Next"; then select a sort order by "Subject" in ascending order; then click "Next"; then shrink the column widths to be as narrow as possible and still be readable; then click "Next"; then check "Enable data integrity"; then click "Finish"; then click "Yes" to save the table.

      5. Then give the "Grades" table a third field: "Grade," of type "Short Text."

      6. We will now define the primary key for the "Grades" table. Note that each record of the "Grades" table is uniquely identified not by any one field but by a combination of the two fields "Student" and "Subject." Therefore, let's make the combination of these two fields the primary key, as follows: While still in the Design vew, highlight the two boxes to the left of the names of these fields. (Highlight one of these boxes, then use the up or down arrow key to highlight the other one too.) Then right-click and select "Primary Key." Make sure that a key icon appears to the left of BOTH "Student ID" and "Subject ID."

      7. Then save the "Grades" table and go to the Data Sheet view.

      8. In the Data Sheet view, enter grades for every possible combination of "Student" and "Subject". Notice how the data cells in the "Student" and "Subject" columns contain drop-down menus, thereby allowing you to select only those students and subjects that are already in the other tables. (This is one of the ways that Access enforces referential integrity.) Also, see what happens if you try to enter two separate grades for the same combination of student and subject. (Access should not allow you to do this if you defined the fields correctly.)

      9. After you've entered a grade for each possible combination of student and subject, save the table.

    When finished with the above, save the entire database (by clicking the "File" tab, then "Save As"), with a filename like the following:

    last-first-elementary.accdb

    replacing "last" and "first" with your own actual last and first name. Submit it as a file attachment to an email message with a subject line like the following:

    HW 9 Access LastName FirstName

    replacing "LastName FirstName" with your own actual last and first name.

  2. HTML assignment (due next week)

    Create three HTML pages that would actually be appropriate to display on a personal website. The three pages must be:

    1. A file named  index.html ,  which will briefly introduce your site and contain (1) a link to our Computer Science 12 website and (2) links to both of the two other twp pages described below. (You may re-use and expand on the version of  index.html  that you created in the the HTML portion of homework assignment 8.)
    2. A file named  aboutme.html ,  which will briefly introduce you and contain (1) links to at least two websites pertaining to your life and/or topics you are interested in, (2) a link to your blog for this class, and (3) a link back to  index.html .  (You may re-use and expand on the version of  aboutme.html  that you created in the the HTML portion of homework assignment 8.)
    3. At least one other page about any topic you like, within reason. The topic does not need to be computer-related. The topic can be any hobby of yours, for example, or something relevant to your career goals, or something relevant to another course you're taking, or any other topic you like, as long as it stays within Queens College's computer use policies. This page must contain at least two sentences of text in addition to the required links, which are: (1) links to at least two remote websites about the page's topic, and (2) a link back to your main index page.

    To summarize the required links:  index.html  must contain a link to our CS 12 course website plus local links to both of the other two pages, both of which pages, in turn, must contain links back to  index.html ,   Each of the other two pages must contain both a link back to  index.html  and at least two remote links to other websites.

    When you are finished, put all your HTML files into a ZIP file with the following filename:

    • last-first-website-9.zip

    where "last" and "first" should be replaced by your own last and first name. Submit the ZIP file, by no later than the beginning of our next class session, via an email message with a subject line like the following:

    HW 9 HTML, LastName FirstName

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

  3. Wordpress blog assignment (due next week)

    Log in to the account you created last week on Wordpress.com, or create a new account there if you did not do so earlier. (See the blog portion of homework assignment 8.)

    You will now be asked to explore some Wordpress themes, which determine the overall appearance of your blog. You will be asked to choose a theme for your blog and also write a new blog post briefly voicing your thoughts and feelings about three specific WordPress themes (which you should mention by name). For detailed instructions, see Selecting a WordPress Theme in Notes on Wordpress.

    Then copy and paste the URL of your blog (http://username.wordpress.com/ replacing "username" with your actual Wordpress username) into an email message. (Do not submit a file.) Your email must have a subject line with the following format:

    HW 9 Blog, LastName FirstName

    replacing "LastName" and "FirstName" with your own actual last and first names.

  4. Reminder: Paper on problem-solving, draft 3 (due next week)

    See instructions here.

  5. Reminder: Powerpoint slide show, draft 2 (due two weeks from now)

    See instructions here.

  6. Excel extra-credit assignments (due two weeks from now):

    See instructions here.

  7. Extra-credit project proposals:

    You may propose an extra-credit project relevant to your major and/or career goals. Possible examples include, but are not limited to, the following:

    • For K-12 education majors:  Write a paper comparing Scratch to the programming capabilities provided in the lessons for grades 1 to 6 on Code.org.
    • For art majors:  Write a paper comparing Scratch to some commonly-used animation software used by professional animators.
    • For political science majors:  Write a paper about the security issues involved in electronic voting.

    Alternatively, you may propose an extra-credit project that deepens your knowledge of the core material of this course and also helps us improve this course for future semesters. Examples include:

    • Find three YouTube video tutorials on one of the Excel topics we've covered this semester. Write a review of the three videos, comparing them in various ways.
    • Do the equivalent of either Access assignment 6 or Access assignment 9, or both, using the open-source Libre Office "Base" database program. Write a tutorial explaining in detail how to do it, similar to the instructions for the Access assignment (s). (You will be thanked by name, in addition to receiving extra credit, if your tutorial or something closely based on it is used in next semester's version of this course.)

    Details of the above, or any similar proposals, must be negotiated via email (and/or in person after class) before you write the paper. Do NOT submit a paper out of the blue and expect to be given extra credit for it. Send an email to the usual homework email address with a subject line like the following:

    Extra credit proposal, LastName FirstName


To all information for Week 9  |  To all homework assignments



Assignment 10 (most parts due before next week's class)

  1. Excel assignment (due next week, Sunday, April 26)
  2. Uploading your HTML pages to a remote Unix machine (due next week, Sunday, April 26)
  3. Blog assignment (due next week, April 26)
  4. PowerPoint presentation, draft 2 (due next week, Sunday, April 26)
  5. Reminder: Excel extra-credit assignments (due Sunday, April 26)
  6. Reminder: Extra-credit project proposals
  1. Excel assignment (due next week)

    Practice using VLookup as in this week's Excel tutorials. Practice first by duplicating one or more of the examples in the tutorials.

    Then modify your old "invoice" file (one of the versions with both taxable and non-taxable items, preferably Assignment 7, but otherwise Assignment 4) so that it contains a separate "Inventory" table (located further down in the spreadsheet, below the invoice itself, with at least four or five blank rows between it and the invoice), listing products together with their unit prices, quantity in stock, and the column indicating whether a given item is taxable. See the second page of A-Invoice-blank.pdf in the example files.) The new inventory table should also contain at least a few more products besides the ones in the invoice. (The idea here is that the inventory table lists all the products in the entire store, whereas the invoice lists only the products bought by one customer on one day. It is highly unlikely that an individual customer would buy every product in the store even if stocking up for an expected quarantine.)

    Then, in the original invoice table itself:

    1. Replace all product names (in the invoice) by formulas consisting of just an equals sign followed by a cell reference to a cell in the inventory table containing the same product name. (This will ensure consistent spelling between inventory and invoice.) In the invoice spreadsheet portrayed on the first page of the example file, one appropriate such formula might be:  =B30
    2. Replace the hard-coded numbers in the "unit price" column of the original invoice table with VLookUp formulas (in the invoice table) which look up the unit price in the separate inventory table.
    3. Likewise, replace the hard-coded T's and blanks in the "taxable" column of the original invoice table with VLookUp formulas which look up whether an item is taxable.

    Make sure the VLookUp formulas are correctly draggable. (Important note: The single-cell product name formulas CANNOT be draggable because there are more products in the inventory than in the invoice, and there is no reason to expect customers to limit themselves to buying sets of products that happen all to be listed next to each other in the inventory. But the VLookUp formulas can and should be draggable.) In order to be correctly draggable, the VLookup parameter "table array" must encompass the data portion of the inventory table, but the "lookup value" must be a cell in the same row of the invoice table where the formula itself is.

    Then modify your old "roster" file (from Assignment 4) to contain an additional table which converts percentage scores to letter grades. (See A-Roster-grade-table.pdf in the example files.) Add a column to the main roster table for the letter grade, and use the separate table, with VLookup, to compute each student's letter grade, based on the final numeric score. Make sure your formula is correctly draggable.

    When you are finished, submit both your "invoice" file and your "roster" file as attachments to a single email message with the following subject line:

    HW 10 Excel LastName FirstName

    (replacing "LastName" and "FirstName" with your own actual last and first names). The files must have filenames like the following:

    • last-first-invoice-10.xlsx
    • last-first-roster-10.xlsx

    (replacing "last" and "first" with your own last and first name).

  2. Uploading your HTML pages to a remote Unix machine (due next week)

    Upload your HTML pages as instructed in this week's tutorials on uploading your website to a remote Unix machine.

    Make sure all your files are visible, and make sure all your links work.

    Copy and paste the URL of your website into an email message with a subject line like the following:

    HW 10 website LastName FirstName

    replacing "LastName FirstName" with your own actual last and first name. (Do not submit a file.)

  3. Wordpress blog assignment (due next week)

    Log in to the account you created two weeks ago on Wordpress.com, or create a new account there if you did not do so earlier. (See the blog portion of homework assignment 8.) Also you'll be able to do the following only if, last week, you succeeded in installing, on your blog, a new theme that supports widgets.

    You will now be asked to add two widgets to your blog, "Categories" and "Tag cloud," both of which are ways of organizing your blog posts by topic. The "Categories" widget is like a table of contents, containing links to groups of posts grouped by their main topic, whereas the "Tag cloud" widget is more like an index, containing links to groups of posts grouped by topics they touch on, not necessarily their main topics. For detailed instructions, see Tags and categories, as widgets in Notes on Wordpress.

    Then copy and paste the URL of your blog (http://username.wordpress.com/ replacing "username" with your actual Wordpress username) into an email message. (Do not submit a file.) Your email must have a subject line with the following format:

    HW 10 Blog, LastName FirstName

    replacing "LastName" and "FirstName" with your own actual last and first names.

  4. Paper on problem-solving, draft 3 (due next week)

    See instructions here.

  5. PowerPoint presentation, draft 2 (due next week):

    See the description of the new section of the PowerPoint presentation you were told to add in the Week 8 assignment.

    Your PowerPoint slideshow, with the new section added, must have a filename with the following format:

    • last-first-ppt-10-draft-2.ppt

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

    Submit the file attached to an email message with a subject line like the following:

    PPT slideshow Draft 2, LastName FirstName

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

  6. Reminder: Excel extra-credit assignments (due tnext week):

    See instructions here.

  7. Reminder: Extra-credit project proposals):

    See instructions here.


To all information for Week 10  |  To all homework assignments



Assignment 11 (most parts due before next week's class)

  1. Excel practice
  2. Word: Adding table of contents, bibliography, and (inline) citations to your paper (due next week, May 3)
  3. PowerPoint presentation, draft 2 (due next week, May 3)
  4. Website tutorial
  5. Reminder: Extra-credit project proposals

  1. Excel practice:
  2. Experiment with the PMT, PV, and FV functions, applying them both to loans and to savings accounts with interest. Also, try varying the period of the payments (weekly, monthly, quarterly, annual).

    You are not expected to submit any of this as homework, via email, but you are expected to become fluent enough with the PMT, PV, and FV functions that you can use them on a quiz.

  3. Word: Adding table of contents, bibliography, and (inline) citations to your paper (due next week):
  4. IMPORTANT NOTE: This assignment MUST be done using the downloadable software version of Microsoft Word, e.g. the free Microsoft Word software download that you can do from your official QC email account. Do NOT use the online version of Microsoft Word (Office 365).

    After you submit your paper on problem-solving (see the instructions for Paper on problem-solving, part 3) and receive feedback on it, submit it again, this time adding automatically generated table of contents, bibliography, and inline citations.

    The "Draft 3" version of your paper was required to have a total of four or five main sections: (1) a section on the problem you chose at the beginning of the semester; (2) a section on the week 5 invoice problem, with the discount; (3) a section on the week 5 sales force problem, with employee ranking; and (4) an introduction and/or conclusion containing a general discussion of problem-solving techniques, of which your approaches to the three Excel problems are examples. Each of these four or five main sections should have a heading. At least one of these four or five main sections must also have at least two sub-sections, with sub-headings. Your paper must also contain at least one quote from an online problem-solving tutorial, with appropriate citation.

    If any of the above was not yet done in your "Draft 3" version of the paper, add it now.

    You will now be asked to create an automatically-generated table of contents that will automatically list both the four (or five) main headings and the sub-headings. Before you can create such a table of contents, you must first use appropriate heading styles, as instructed in this week's tutorials. Use the "Heading 1" style for each of the four (or five) main headings, and use the "Heading 2" style for each of the sub-headings.

    You will now be asked to learn how to create an auto-generated bibliography and anto-generated inline citations, which will take a lot of the drudgery out of any future research papers you may write. As instructed in this week's tutorials, use Word's "Manage Sources" feature to enter data for both your bibliography and citations.

    The paper must be a Microsoft Word document with a filename with the following format:

    where "last" and "first" should be replaced by your own last and first name. Also, together with the paper, re-submit the spreadsheet (or a corrected version thereof) that was the focus of Part 1 of your paper. Give the spreadsheet a filename like the following:

    again replacing "last" and "first" with your own actual last and first names. Submit both files attached to a single email message with a subject line like the following:

    HW 11 Paper Draft 4, LastName FirstName

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

    For full credit, your paper must show evidence of having used the automatic features mentioned above. To that end, once you add those features, DO NOT edit your paper using any word processing software other than Word 2010 or later, and DO NOT edit your paper using the online version of Microsoft Word (Office 365). If you edit the paper using an incompatible version of Microsoft Wrod, the table of contents, citations, and bibliography will remain, but the evidence of automatic generation will disappear -- it will look like you did them the painstaking old-fashioned way.

  5. PowerPoint presentation, draft 2 (due next week):
  6. See the description of the new section of the PowerPoint presentation you were told to add in the Week 8 assignment.

    Your PowerPoint slideshow, with the new section added, must have a filename with the following format:

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

    Submit the file attached to an email message with a subject line like the following:

    PPT slideshow Part 2, LastName FirstName

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

  7. Website tutorial:

    Study the Tutorial on the components of most websites and be prepared to answer questions about it on the upcoming quiz.

  8. Reminder: Extra-credit project proposals:

    See instructions here.


To all information for Week 11  |  To all homework assignments



Assignment 12 (due next week)

  1. Excel practice
  2. Number base conversions practice
  3. Problem-solving paper, final version (due next class, May 10)
  4. PowerPoint presentation, final version (due next class, May 10)
  1. Excel practice:

    This week there is no Excel assignment to submit for grading, but, in preparation for the quiz, you should give yourself plenty of practice with string concatenation and the greater flexibility that string concatenation gives you when using COUNTIF, SUMIF, and AVERAGEIF.

    Remember that the criterion, when NOT an exact match, is a string containing special characters, e.g. a string such as  ">100"  which begins with a comparison operater. The criterion can thus be created using string concatenation. Only with string concatenation can, for example, a COUNTIF function have the ability to count all the values in a range that are less than the value in some specified cell.

  2. Number base conversions practice:

    As part of your preparation for next week's quiz, you should review how to convert binary (binary (base 2) numbers to decimal (base 10) numbers and vice versa, and how to convert binary numbers to hexadecimal (base 16) numbers and vice versa. Give yourself some practice with all four of these kinds of number base conversions.

    Begin by working through all the examples in all the tutorials listed in the section on "Binary Numbers" in Some basics of how computers work. Then choose several arbitary numbers between 0 and 256 (base 10) and convert each number from decimal to binary, and then from binary back to decimal, verifying that you get back the original number. Also practice converting each of the resulting binary numbers to hexadecimal and then back to binary.

    You are not required to submit these exercises on number base conversions. Just do them in preparation for the quiz.

  3. Problem-solving paper, final version (due next class):

    The final version of your paper on problem-solving will be worth 12% of your final score for the course.

    After you receive feedback on last week's version of the paper on problem-solving (see the instructions for adding the automatically-generated table of contents, bibliography, and citations), submit your final version, making all requested changes. Review all the feedback you got on previous versions too, and make sure you've made all the necessary corrections.

    IMPORTANT: For full credit, your paper must show evidence of having used the automatic features mentioned above. To that end, once you add those features, DO NOT edit your paper using any word processing software other than the exact same version of Microsoft Word that you used when doing the previous assignment involving automatically-generated table of contents, bibliography, and citations. If you edit the paper using an incompatible version of Microsoft Word, or if you edit it using a different word-processing program such as Libre Office, then the table of contents, citations, and bibliography will still be there, but the evidence of automatic generation thereof will disappear -- it will look like you did them the painstaking old-fashioned way.

    The paper must be a Microsoft Word document with a filename with the following format:

    • last-first-prob-solve-5.docx

    where "last" and "first" should be replaced by your own last and first name. The accompanying spreadsheet for Part 1 should likewise have a filename with the following format:

    • last-first-prob-solve-5.xlsx

    Submit these files attached to an email message with a subject line like the following:

    HW 12 Paper 5, LastName FirstName

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

  4. PowerPoint presentation, final version (due next class):

    The final version of your PowerPoint slideshow will be worth 6% of your final score for the course.

    After you receive feedback on the Week 10 version of the PowerPoint assignment, submit the final version, making any requested changes.

    Your PowerPoint slideshow, must have a filename with the following format:

    • last-first-ppt-3.ppt

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

    Submit the file attached to an email message with a subject line like the following:

    HW 12 PPT 3, LastName FirstName

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


To all information for Week 12  |  To all homework assignments



Assignment 13 (extra-credit assignments)

  1. Deadline extended for extra-credit assignments (to Friday, May 22, 2020, at 6:00 PM)
  2. Deadline extended for Assignment 12 (to Friday, May 22, 2020, at 6:00 PM)
  3. Practice with Microsoft Word's Mail Merge

Deadline extended for extra-credit assignments:

You may still do the following extra-credit assignments:

  1. Week 8 Excel extra-credit assignments (project due Friday, May 22, 2020, no later than 6:00 PM)
  2. Week 9 Extra-credit project proposals (preliminary email due before final exam; project itself due Friday, May 22, 2020, no later than 6:00 PM; project MUST NOT be submitted without preliminary email due before final exam).

Deadline extended for Assignment 12:

The deadline for the assignment 12 versions of the Problem-solving paper, final version and the PowerPoint presentation, final version has been extended to Friday, May 22, 2020, no later than 6:00 PM. If there are also other, previous drafts you have not yet done, please try to get them done well before the above extended deadline, so you can get feedback on them before submitting your final version.

Practice with Microsoft Word's Mail Merge:

You will not be asked to submit an assignment using Mail Merge. But you should practice with it enough to make sure you understand what it is and how to use it.


To all information for Week 13  |  To all homework assignments

D. Nixon: CS 12 > Assignments