Computer Science 12
Week 12
May 3, 2020
 Assignment 12 (due next week)
 Example files for this week
 Tutorials on Excel (string concatenation, and criteria revisited)
 Tutorial on computer basics (general)
 Tutorial on the structure of most modern websites
 Study guide for next week's quiz
Assignment 12 (due next week)
 Excel practice
 Number base conversions practice
 Problemsolving paper, final version (due next class, May 10)
 PowerPoint presentation, final version (due next class, May 10)
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.
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.

Problemsolving paper, final version (due next class):
The final version of your paper on problemsolving will be worth 12% of your final score for the course.
After you receive feedback on last week's version of the paper on problemsolving (see the instructions for adding the automaticallygenerated 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 automaticallygenerated 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 wordprocessing 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 oldfashioned way.
The paper must be a Microsoft Word document with a filename with the following format:
lastfirstprobsolve5.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:
lastfirstprobsolve5.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.

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:
lastfirstppt3.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
Example files for this week
Some example files will be uploaded here.
More may appear here before and during our online class session today (April 12).
To all information for Week 12  To all example files
Tutorials on Excel (string concatenation, and criteria revisited)
String concatenation (joining multiple text strings into one):
 How to Join Text Strings in Excel 2010 with the & Operator  dummies
 CONCATENATE and CONCAT functions  Microsoft
 MS Excel: How to concatenate strings together  Tech on the Net
 How to join values with the ampersand  ExcelJet
 CONCATENATE in Excel: combine text strings, cells and columns  AbleBits
COUNTIF (revisited):
 The Excel CountIF Function  Home and Learn .
 COUNTIF in Excel  Ablebits
 Excel COUNTIF Function  ExcelJet
 COUNTIF function  Microsoft
SUMIF (revisited):
 The Excel SumIF Function  Home and Learn .
 Excel SUMIF Function  ExcelJet
 SUMIF function  Microsoft
AVERAGEIF (revisited):
 How to use the AVERAGEIF Function  Tech on the Net
 Excel AVERAGEIF Function  ExcelJet
 AVERAGEIF function  Microsoft
To all information for Week 12  To all tutorials about Excel
Tutorial on computer basics (general)
Some basics of how computers work, by Marcas Neal and D. Nixon.
To all information for Week 12  To all tutorials about computer basics, operating systems, and nonweb Internet
Tutorial on the structure of most modern websites
Structure of a typical modern website:
To all information for Week 12  To all tutorials about the World Wide Web
Study guide for next week's quiz
The May 10 quiz will be an online quiz containing the following:
There will be some spreadsheet problems involving string (text) concatenation in Excel. You will be asked to write Excel formulas that may involve string concatenation, and there will likely also be some multiplechoice and/or fillintheblanks questions involving string concatenation in Scratch as well as Excel.
Make sure you understand the following similarities and differences amongst the Excel and Scratch versions of string (text) concatenation: (1) Excel uses the '&
' operator (or the CONCATENATE function), whereas Scratch uses the "join" block in the "Operators" category of the block palette. (2) Both Excel and Scratch can automatically convert nonstring values to strings before concatenating them (but be aware that many programming languages do NOT do this automatically, but instead require all the concatenated values to be strings already, thus requiring the programmer to explicitly convert numeric or other nonstring values to strings before concatenating them). (3) Even in Excel, outside the context of string concatenation, automatic conversion of strings (text values) to numeric values does not necessarily happen in every context where a novice Excel user might expect it to happen. (You might have run into this issue when doing homework, e.g. when doing the HW 7 salesforce problem, if you computed the rank as text values rather than numeric values and then tried to use them as numbers in the summary table.)
There also will be one or more spreadsheet problems involving Excel's COUNTIF, SUMIF, and AVERAGEIF functions. Make sure you understand the greater flexibility that string concatenation gives you when using these functions. 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. Such a string can be created using string concatenation, rather than just using a single string literal. Only with string concatenation can, for example, a SUMIF function have the ability to sum all the values in a range that are greater than the value in some specified cell.
There will also be one or more other spreadsheet problems, which may involve any of the other Excel topics covered so far. Be especially prepared for problems involving the LARGE and SMALL functions and the financial functions PMT, PV, and FV. Also be prepared for problems involving VLookup, the daterelated functions ((DATE, TODAY, DAYS360, YEARFRAC, and the use of simple subtraction to get a time interval in actual days), and the logical functions (IF, AND, OR) including nested or otherwise multiple IF. The questions may include fillintheblanks and/or multiple choice questions and/or a problem in which you are asked to write an appropriate formula to accomplish some particular calculation.
Regarding Scratch, you may be shown a script and asked questions about what it does and/or how it could be changed to do some other thing. You might be asked which block is needed to perform some task, and/or what needs to be put in the holes of said block. Such questions may involve any of the blocks you are likely to have used in the homework assignments, if you did them correctly.
There will also be some multiplechoice and fillintheblanks questions about the contents of both of the tutorials The components of most websites and Some basics of how computers work. Any part of these tutorials is fair game, including number base conversions, RGB color codes, and binary representations of numbers and text, which were covered earlier in the semester and which you should review. You will be expected to recognize (or easily look up) the hexadecimal RGB codes for the colors listed in the tutorial here. There will also be one or more number base conversion problems as well as questions about various other topics covered in these tutorials.
New topics covered in Some basics of how computers work that are fair game on the quiz include: amounts of memory, disk space, and time; categorization of computers by size; "Moore's law"; primary vs. secondary storage (e.g. RAM vs. disk); other computer hardware components; input and output devices; the system "bus" (and its three component "buses") connecting all the computer's hardware components and the I/O devices together; hardware vs. software; and types of software. Make sure you understand the differences between primary and secondary storage, and make sure you understand what "Moore's Law" is and why it has been slowing down lately. Be sure you understand the meanings of all words emphasized (bolded and undrlined, or bolded and italicized) in the tutorials.
Regarding The components of most websites, the topics you will be responsible for include: the relationship between the Internet and the World Wide Web, the three network models, static vs. dynamic content; front end vs. back end; the various languages that can be used on a website (including programming vs. nonprogramming languages and front end vs. back end languages); and the use of a database as part of the back end on many websites.
To all information for Week 12  To all quiz and exam study guides