Computer Science 12
Sunday, February 16, 2020
- Assignment 3 (most parts due next week)
- Computer basics tutorial
- Excel tutorials
- More Scratch animation examples
- Study guide for next week's quiz
- Excel assignment (due February 23)
- Scratch assignment (due February 23)
- Number base conversions practice
- Powerpoint presentation (due February 23)
- Problem-solving paper, part 1 (due March 1, but get your chosen problem approved ASAP)
- Excel assignment (due next week):
- Continuing the grade roster assignment from last week:
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.
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:
where "last" and "first" must be replaced by your own last name and and first name, respectively.
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:
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.
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:
last-first-birthday-card.sb2(includes additional step in Notes on the "Birthday Card" tutorial)
last-first-make-it-fly.sb2(includes additional step in Notes on the "Make It Fly" tutorial)
last-first-make-it-fly-revisit.sb2(includes additional step in The "Make It Fly" tutorial revisited)
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.
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.
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.
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.
On computer basics, including binary numbers, hexadecimal numbers, and number base conversions, see Some basics of how computers work, by Marcas Neal and D. Nixon. See especially the section on Binary numbers.
On Excel charts:
- Excel Charts in Microsoft Excel 2007 Tutorial by Denise Etheridge
- "Section Three - Excel 2007 Charts" in Microsoft Excel 2007-2016 Course on Home and Learn's Free computer Tutorials site.
- Charts and Graphics in MS Excel 2007 Tutorials on brainbell.com
- How to Use Charts and Graphs in Excel, including How to Create and Format a Pie Chart in Excel and How to Create a Column Chart in Excel on ThoughtCo.com.
On Excel tables:
- How to create a table in Excel 2007 on the "for Dummies" site
- Table section in Microsoft Office Excel 2007 Tutorial on a "Java examples" site
- Working with Tables in Excel 2007 on the website of JKP Application Development Services
- Excel date tutorials on ThoughtCo.com, including: (1) Properly Enter Dates in Excel with the DATE Function, (2) Count Days Between Two Dates in Excel, (3) An Overview of Serial Number and Serial Date in Excel
- The Date Function in Excel 2007 on Home and Learn
- The Excel TODAY Function on ExcelFunctions.net
- Excel DATE Function and Excel TODAY Function on ExcelJet
- DATE function and TODAY function on the Microsoft site
- Excel TODAY function to insert today's date and more and Excel date functions - formula examples of DATE, TODAY, etc. on AbleBits
- How to use the DATE Function (WS) and How to use the TODAY Function (WS) on Tech on the Net
- Excel TIME Function on ExcelJet
- TIME function and Insert the current date and time in a cell and Date and time functions (reference) on the Microsoft site
- Date & Time Functions on Excel Easy
- How to use the TIME Function (WS) on Tech on the Net
More about dates in Excel, including DAYS360 and YEARFRAC functions, and the financial conventions they are based on:
- Online Tech Tips: How to subtract dates in Excel and How to use the YEARFRAC function in Excel.
- Wikipedia articles on Day count convention and 360-day calendar
- DAYS360 function and YEARFRAC function on the Microsoft site
- The Excel DAYS360 Function and Excel YEARFRAC Function on ExcelFunctions.net
For an example spreadsheet showing various different ways of subtracting dates to get the length of a time interval in days (via simple subtraction vs DAYS360) or years (via DAYS360 or YEARFRAC with default basis vs. YEARFRAC with basis = 1), download this Excel file.
Line breaks in formulas (as well as in literal text in cells):
- Excel - Insert a Line Break in a Cell - Spreadsheets Made Easy
- How to start a new line within a cell - Office Hack
- Add a line break with a formula - ExcelJet
- Add Line Break in Excel Formula (and how to enable word wrap) - Contextures
- Adding a Line Break within an Excel Formula - For Dummies
- Line Break in a Cell Using a Formula by Jon Wittwer on 3/14/2009
It is recommended that you also use Excel's Help feature to get more information on all of the above.
See also the Excel tutorials listed for Week 1.
See Scratch's built-in tutorials, listed on the right hand side of your Scratch window when you click "Tips" in the menu at the top left.
Beginning on Sunday, September 29, we will have brief (10 to 20 minute) quizzes at the beginning of each class session. (Please arrive on time or early.) The quizzes will be on paper.
The September 29 quiz will be an on-paper quiz having three parts:
- Excel: Given a picture of a portion of an Excel spreadsheet, you will be expected to write appropriate formulas for the specified cells. The formulas will involve some of the functions that have been covered in lab and/or in tutorial material. These may include SUM, COUNT, AVERAGE, MIN, MAX, and the various functions involving dates. They may also include the basic arithmetic operations (addition, subtraction, multiplication, and division). There may also be some fill-in-the-blanks and/or multiple choice questions about various different ways of subtracting dates (simple subtraction, DAYS360, YEARFRAC with default basis, YEARFRAC with basis = 1) to get the length of a time interval in days or years. Also you will need to know the difference between absolute and relative addressing of cells (e.g. $A$2 vs. A2).
- Scratch: There will be some fill-in-the-blanks and/or multiple choice problems involving Scratch. To prepare, review the assignments you did these past few weeks, and then read the Scratch Wiki pages linked here. Know the Scratch-specific meanings of the terms "sprite", "costume", "script", "block", and "block palette", and be prepared to answer basic questions about specific blocks similar to the ones you used in the assignmnets during the first three weeks. Also, be prepared to answer fill-in-the-blanks or multiple choice questions about the differences between vector graphics and raster (a.k.a. bitmap) graphics, about which see tutorials listed here as well as here
- Computer basics: There will be some fill-in-the-blanks and/or multiple choice questions about the section on Binary numbers in the tutorial Some basics of how computers work. Make sure you understand what binary numbers are and their role in computers. You will likely be asked to convert a small binary (base 2) number to a small decimal (base 10) number, or vice versa. You will likely also be asked to convert a small binary number to or from its hexadecimal (base 16) equivalent. Also, make sure you understand the meaning of all the terminology that appears in boldface type within that section of the tutorial. There may be fill-in-the-blanks or multiple choice questions about these terms.
To prepare for the Excel part of the quiz, you will need practice using all the above-named functions and features. To that end, it is recommended that you not only do the homework but also play with examples in the Excel tutorials for all three weeks up to now.