Computer Science 12
Sunday, March 22, 2020
- Assignment 7 (due next week)
- Excel tutorials
- Tutorial on Scratch
- Tutorials on non-Web Internet file transfer
- Example files for this week
- Study guide for next week's quiz
- Excel assignment (due next week, April 5)
- Scratch assignment (due next week, April 5)
- Non-Web Internet file transfer
- 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.)
- Excel assignment (due next week):
- 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.
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:
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.
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:
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.)
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.
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.
- The Excel CountIF Function - Home and Learn .
- COUNTIF in Excel - Ablebits
- Excel COUNTIF Function - ExcelJet
- COUNTIF function - Microsoft
- The Excel SumIF Function - Home and Learn .
- Excel SUMIF Function - ExcelJet
- SUMIF function - Microsoft
- How to use the AVERAGEIF Function - Tech on the Net
- Excel AVERAGEIF Function - ExcelJet
- AVERAGEIF function - Microsoft
COUNTIFS, SUMIFS, and AVERAGEIFS:
- Excel COUNTIFS: A Step by Step Tutorial on How to Use COUNTIFS in your Worksheets - Brigitta Schwulst
- How to use Excel COUNTIFS and COUNTIF with multiple criteria - Ablebits
- Excel SUMIFS: A Step-by-Step Tutorial on How to Use the SUMIFS Function - Brigitta Schwulst
- Excel SUMIF and SUMIFS - Explained - Excel Trick
- How to use the Excel Functions: SUMIF and SUMIFS (tutorial) - SpreadSheetO
- How to use the AVERAGEIFS Function - Tech on the Net
- How to use the AVERAGEIFs function - ExcelJet
- Excel Names - Named Ranges - Contextures
- Define and use names in formulas - Microsoft
- Assigning a Range Name in Excel - ProductivityPortfolio
- Excel pro tips: How to create, define, and use Named Ranges - PCWorld
- How to Use Excel Range Names to Build Formulas - dummies.com
- Range Names In Excel - JKP
- 20 tips for Named Ranges in Excel - ExcelJet
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.
- Non-Web Internat protocols for transfering files:
- Installation of an SFTP/SCP program
- Logging in to the CS 12 Unix (Linux) machine
- General tutorials on WinSCP and FileZilla
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.
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:
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":
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.
General tutorials on WinSCP and FileZilla:
Below are some tutorials on WinSCP:
- Guides to using WinSCP (see Setup and Basic Tasks)
- SiteGround WinSCP Tutorial
- FastComet: How to configure WinSCP and How to upload files using 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.edurather than whatever host name is indicated in the tutorial.
Some example files will be uploaded here.
More will appear here before and during our online class session today (March 22).
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.
Excel problems will require not only that you write formulas, but also that you answer questions about them, including some open-ended "how" and "why" questions.
- Excel problems may involve all of the functions that we've covered so far, including not just functions newly covered this week -- COUNTIF, SUMIF, and AVERAGEIF -- but also SUM, COUNT, AVERAGE, MIN, MAX, IF (including nested IF or other multiple IF), AND, OR, and also the basic arithmetic operations (addition, subtraction, multiplication, and division), plus the date functions (DATE, TODAY, YEARFRAC, DAYS360, and the use of simple subtraction to get a difference in actual days).
There will be some questions -- including but not limited to fill-in-the-blanks questions -- on database terminology. Any of the basic database concepts covered in class will be fair game. Know the meaning of the following terms, in the context of databases: "table," "record" (a.k.a. "tuple"), "field" (a.k.a. "attribute"), "data type", "key," "primary key," "query," "form," "report." This week you will also be expected to know the meanings of two additional terms -- "foreign key" and "referential integrity" -- which are relevant to multi-table databases.
Scratch: There will likely be some questions about the various Scratch blocks that have been used in tutorial exercises, and what categories they are from. Also you may be shown one or more pictures of portions of Scratch scripts, about which you may be asked fill-in-the-blanks or multiple-choice questions about what the scripts do and how.
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.