D. Nixon: CS 12 > Week 7

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

Computer Science 12


Week 7




Sunday, March 22, 2020



Assignment 7 (due next week)

  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



Excel tutorials

COUNTIF:

SUMIF:

AVERAGEIF:

COUNTIFS, SUMIFS, and AVERAGEIFS:

Range names:

Note: If you use Excel range names, cell names, etc. on a quiz or exam, you must provide a table indicating what range(s) or cell(s) your name(s) refer to.


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



Tutorials on Scratch

Scratch interactive animation example.


To all information for Week 7  |  To all tutorials about programming (very basic intro)



Tutorials on non-Web Internet file transfer

  1. Non-Web Internat protocols for transfering files:
  2. Installation of an SFTP/SCP program
  3. Logging in to the CS 12 Unix (Linux) machine
  4. General tutorials on WinSCP and FileZilla
  1. Non-Web Internat protocols for transfering files:

    The Internet is not the same thing as the World Wide Web. The World Wide Web (the Internet protocol that enables you to view pages in a web browser) is only the most popular way of using the Internet. There are also other, older ways of using the Internet, such as File Transfer Protocol (FTP).

    You will now be taught how to use a variant of FTP to upload your HTML files to the CS 12 Unix (Linux) machine, so you can view them as a website there.

    The original FTP protocol was not secure. Usernames, passwords, and the files themselves were all sent unencrypted. It is now standard for all access to passworded accounts over the Internet to be encrypted. To that end, the original FTP protocol has been largely superceded by SFTP, which stands for "Secure File Transfer Protocol." Another, very similar protocol is "SCP," which stands for "Secure Copy." WinSCP can do both SFTP and SCP.

  2. Installation of an SFTP/SCP program:

    Before you can upload your website to the CS 12 Unix (Linux) machine, you will first need an SFTP file transfer client program. In the lab at school, WinSCP is is already installed. If you are doing this at home, you must download and install one of the programs below:

  3. Logging in to the CS 12 Unix (Linux) machine:

    Make sure you can log in to tue Unix machine for this class. Use the following as the "Host name":

        cs12.cs.qc.cuny.edu
    

    Use the same username and password that we use on the machines in the lab. Make sure you type the letters of your username in all lower case. Unix is case-sensitive.

    If you have difficulty figuring out how to log in, refer to the tutorials below.

  4. General tutorials on WinSCP and FileZilla:

    Below are some tutorials on WinSCP:

    Below are some tutorials on using SFTP with FileZilla (but see note below):

    Note: Both of the above FileZilla tutorials are tailored for specific sites that we are not using. Ad noted above, the host name you should use is  cs12.cs.qc.cuny.edu  rather than whatever host name is indicated in the tutorial.


To all information for Week 7  |  To all tutorials about computer basics, operating systems, and non-web Internet



Example files for this week

Some example files will be uploaded here.

More will appear here before and during our online class session today (March 22).


To all information for Week 7  |  To all example files



Study guide for next week's quiz

Instead of on-paper quizzes, there will now be a class participation grade based on your participation in our online class via Zulip chat.

For each week starting on March 29, half of your class participation grade will be based in your participation, during the first half hour, in what will essentially be an open-book quiz on previously-covered material. During that time, you will be asked questions in the "class session" stream that you will be asked to answer in your private stream. Because this is an open-book quiz, the questions will be more in-depth and require more thinking than the kinds of questions you were asked on the paper quizzes.

Every topic previously mentioned in the Quiz 4 study guide should also be considered fair game, because on-paper Quiz 4 was cancelled due to the CoViD-19 crisis.


To all information for Week 7  |  To all quiz and exam study guides

D. Nixon: CS 12 > Week 7