Computer Science 12
Sunday, February 23, 2020
- Assignment 4 (due next week)
- Computer basics tutorial
- Excel tutorials
- Scratch: Intro to variables, if/then/else, and simple games
- Study guide for next week's quiz
- Excel assignment (due March 1)
- Scratch assignment (due March 1)
- Problem-solving paper, part 1 (due date posponed to March 8)
- Excel assignment (due next week):
- Continuing the grade roster assignment from last week:
In the problems below, you will practice having your spreadsheet do automated decision-making via the IF function.
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:
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:
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.
Do the following:
- Scratch's built-in "Make Music" tutorial. See Notes on the "Make Music" tutorial for some clarifications plus a required additional step.
- Intro to variables and if/then/else. (This is a separate, self-contained tutorial, not a supplement to a built-in Scratch tutorial.)
- Scratch's built-in "Race to the Finish" tutorial. See Notes on the "Race to the Finish" tutorial for some clarifications plus a required additional step.
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.
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.
On the use of binary codes for data storage, see the following sections of Some basics of how computers work:
- Amounts of memory, disk space, and time
- Binary representation of numbers and text
- Binary representation of colors
On the Excel IF function:
- The IF Function in Excel 2007 on Home and Learn
- IF function on Microsoft's site
- The Basics of the Excel IF Function on Productivity Portfolio
- Using the IF Function in Excel 2007 by Michele McDonough
- Pages on Customize Cell Data with Excel's IF Function by Ted French on ThoughtCo.com
- Using IF function in Excel: formulas for numbers, text, dates, blank cells - Ablebits.com
Excel's comparison operators:
- Comparison Operator: Excel and Google Spreadsheet Six Comparison Operators by Ted French on ThoughtCo.com
- Excel Operators (including comparison operators, among others) on ExcelFunctions.net
On the data types of Excel values:
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.
See also Notes on some Scratch tutorials, especially the following:
- Notes on the "Make Music" tutorial
- Intro to variables and if/then/else
- Notes on the "Race to the Finish" tutorial
The March 1 quiz will be an on-paper quiz having two or three parts, covering the following topics:
- 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 class and/or in tutorial material. These may include SUM, COUNT, AVERAGE, MIN, MAX, and especially the IF function. They may also include the basic arithmetic operations (addition, subtraction, multiplication, and division), plus the relational operators, (such as
>=), which we've often seen used within logical expressions. Also you will need to know the difference between absolute and relative addressing of cells (e.g. $A$2 vs. A2), and you will need to know the date functions we covered in class (the DATE, TODAY, YEARFRAC (with and without the "basis" parameter), and DAYS360 functions, and the use of simple subtraction to compute the number of days between two dates). Make sure you know how to use both literal text values and literal numeric values within a formula. (A literal text value within a formula needs to be enclosed in quotes, whereas a literal numeric value must not be enclosed in quotes.)
- Scratch: There will be some fill-in-the-blanks and/or multiple choice problems involving Scratch. Know the Scratch-specific meanings of the terms "sprite", "costume", "script", "block", "block palette", and "variable," and be prepared to answer basic questions about specific blocks similar to the ones you used in the assignmnets during the first four weeks. Especially, be prepared to answer questions pertaing to if/then blocks and/or if/then/else blocks. Also, be prepared to answer questions about the use of variables to keep track of data. Review all the exercises you've done so far, and make a note of the various blocks you have used under the following categories on the block palette: "Events", "Control", "Looks", "Motion", "Operators", and "Data". In your Scratch window, examine and play with a wide variety of blocks (not just the ones you've used in exercises) in each of these categories, enough to give yourself a memorable feel for (1) what each category is all about, and (2) which kinds of blocks belong in each category.
- Computer basics: There will be some fill-in-the-blanks and/or multiple choice questions about whatever parts of the following tutorial we have time to cover in class this week: Some basics of how computers work. Again 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.
To prepare for the quiz, you will need practice using all the above-named functions. To that end, it is recommended that you not only do the homework but also play with examples in the Excel tutorials for all four weeks up to now. Make sure, especially, that you get practice with IF function.
Also there will likely be some fill-in-the-blanks or multiple choice questions about the more common ways by which numbers, text, and colors are stored in a computer's memory. For example: (1) Know the general differences between integers and floating-point numbers and how they are stored. Know that floating-point numbers are stored as a binary equivalent of base 10 scientific notation, with a sign bit, an exponent, and a mantissa. (2) As for characters, know that ASCII stores characters as 7-bit binary codes, whereas UCS-2 (a subset of Unicode) stores characters as 16-bit binary codes, of which the first 128 codes represent the same characters as the ASCII codes with the same numeric values. (3) Make sure you understand the basics of RGB color codes. You will be expected recognize the hexadecimal RGB codes for the following colors in particular: white (FFFFFF), black (000000), red (FF0000), green (00FF00), blue (0000FF), and yellow (FFFF00).