Computer Science 12
Week 2
Saturday, February 4, 2012
- Tutorials on Excel
- Tutorials on HTML links
- Tutorials on miscellaneous topics covered this week
- Assignment 2 (paper topic selection due Wednesday, February 8, 2012; list of paper sources due Wednesday, February 15, 2012; paper first draft due before class on Saturday, February 25, 2012; Excel and HTML parts due before class on Saturday, February 25, 2012)
On Excel functions in general, and specifically the SUM, AVERAGE, COUNT, COUNTA, MIN, MAX, and MEDIAN functions:
- Very basic intro: AutoSum and Excel Functions in Microsoft Excel 2007 Tutorial by Denise Etheridge
- Another very basic intro: Performing Calculations in Florida Gulf Coast University's Excel 2007 Tutorial
- Excel Formulas and Functions in Excel 2007 on Quamut.
- Crunching Numbers with Excel 2007's AVERAGE, MAX, MIN, and MEDIAN Functions in Functions on the "for Dummies" site
- Count Functions and Statistical functions in Using Excel Functions in Spreadsheets on About.com
- How to use A functions in Excel (e.g. COUNTA as distinct from COUNT) on the Microsoft site
Line breaks in cells:
- Add A Line Break To A Cell In Excel 2007 on Windows Fanatics
- Line Breaks in Formulas on Daily Dose of Excel
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.
To all information for Week 2 | To all tutorials about Excel
Here are tutorials on HTML links:
- Links - the Pocket HTML Tutorial
- HTML Links - W3Schools tutorial
- Hyperlinks - HTML for beginners
Ses also the general HTML tutorials for week 1.
To all information for Week 2 | To all tutorials about HTML
Tutorials on miscellaneous topics covered this week
Editing HTML files using Windows Notepad:
- About.com: Writing HTML in Notepad
- Northern Virginia Community College: Notes on Using Notepad with HTML
Note: Editing files in Windows Notepad can be done reliably only when filename extensions are visible. See the pages below on how to make filename extensions visible in Windows 7.
Filename extensions in Windows:
- Microsoft: How to show or hide file name extensions in Windows Explorer and File names and file name extensions: frequently asked questions
- Windows 7 Forums: How to Hide or Show Known File Type Extensions in Windows 7
- Safari Books Online: Windows 7: The Missing Manual: Filename Extensions and File Associations
To all information for Week 2 | To all tutorials about miscellaneous topics
- Paper:
last-first-paper.docx- Excel assignment (due before class on Saturday, February 25):
- Making believe you're a teacher, create an Excel spreadsheet with columns as follows: (1) a list of names, (2) five columns of quiz scores, (2) two columns of results to be computed from quiz scores, (3) two columns of in-class exam scores, and (4) the final exam score. The two columns of results computed from the quiz scores must be (a) the average quiz score and (b) the average with lowest score dropped.
last-first-roster.xlsx- In a separate file, write an invoice for goods ordered from a store. The invoice should have columns for product name, per-unit price, quantity ordered, and total price. There should be rows for at least four products. Underneath the last row, below the total price column there should cells (with identifying text in cells to the left of them) for subtotal, tax, and then total. The tax should be computed from the tax rate, which should be placed in a separate cell somewhere.
last-first-invoice.xlsx- HTML assignment (due before class on Saturday, February 25):
Begin creating two HTML pages that you would actually like to display on a small website you'll be creating later in the semester. The two pages must be:
- A file named
index.html, which will contain a link to your other page,aboutme.html. (This page will eventually be the main page of your website. In future assignments, it will briefly introduce your website and contain a list of links to all your other pages.) - A file named
aboutme.html, which will contain a link back toindex.html, and also a link to this website's main page,http://cs12.dnixon.nyclocal.net/. This file should have the title "About me" or "About ____" (replacing the blank with either your real name or a pseudonym) and should say a little about yourself, including what courses you are now taking.
To help you get started, you may begin by modifying the sample HTML file contained in the ZIP file HTML-demo.zip, similar to the file we worked on in last week's lab session. However, please note that the displayed text in the files you create this week must be very different from what is in the attached file. It should no longer contain demo code such as "This is a title" or "This is a paragraph" or "This is bold."
When you are finished, put both your HTML files into a ZIP file with the following filename:
last-first-website.zip
where "last" and "first" should be replaced by your own last and first name.
Submit the ZIP file in the appropriate area on Blackboard by no later than the beginning of class on Saturday, February 25, and make sure have access to the files in lab during our next class session on that day. Note that the very next HTML lesson will build on this assignment; hence the deadline is absolute.
- A file named
Continue work on your paper, as follows: (1) Email me, by no later than Wednesday, February 8, about the topic you have chosen. (2) By no later than noon on Wednesday, February 15, email me your list of sources. (2) Submit the first draft via Blackboard, by no later than the beginning of class on Saturday, February 25. It should have the following filename:
where "last" and "first" should be replaced by your own last name and and first name, respectively.
Your paper may be on any of the computer-related topics that are introduced in Jerry Waxman's lecture notes, other than Microsoft Office products such as Word, Excel, and Access. Your paper must use four sources, including at least one printed offline source (e.g. a physical book from the library) and at least two websites. Your first draft, which must be finished by the beginning of class on Saturday, February 25, must be at least three pages long and must include at least one quote from each of your four sources, and must have at least three sections with headings. We will then show you how to do citations, footnotes, bibliography, and table of contents, using Microsoft 2007/2010 features which make these chores very easy.
For full credit, the first draft must be submitted by the beginning of class on Saturday, February 25. It will also be accepted late up to the beginning of class on Saturday, March 4, but with one point (out of ten) off for lateness.
Make rows for at least five students. The names of your imaginary students, and the quiz scores, will be left to your imagination. Just don't copy them from another student. The quiz scores should range between 0 to 10, while the exam scores should range between 0 and 100.
Save your Excel spreadsheet with a filename having the following format:
where "last" and "first" should be replaced by your own last name and and first name, respectively.
Then format the "Grade Roster" title and "Contact" lines to make them look nice, merging adjacent cells as necessary to eliminate overlap. To the left of the scores, you must have columns for imaginary students' names, first a column for the last names, then a column for the first names. Make up a unique set of names (not used by other Computer Science 12 students). Above the scores, there must be a row for column headings for the scores. The column headings should be: Quiz 1, Quiz 2, Quiz 3, Quiz 4, Quiz 5, Exam 1, Exam 2, and Final Exam.
Then, in between the quiz scores and the exam scores, insert two columns for calculated results involving the quiz scores. The first of these should be a column for the average quiz score. The second of these should be a column which computes an average of quiz scores with the lowest grade dropped. (Hint: Use a formula involving the SUM, COUNT, and MIN functions, not the AVERAGE function.)
In order to do the tax computation, referring to the tax rate in some specific cell, you will need to use absolute addressing, so that your formula will still refer to the tax rate in the same cell when you drag the formula.
All columns should have clear headings. And, somewhere above the headings, there should be cells for the store's name, the date, and the customer's name and address. Use two or more merged cells for the store's name, the customer's name, and each line of the customer's address, so that these will display nicely, and use an appropriate heading format for the store's name. Put your own name and email address in appropriately merged cells at the bottom.
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 in the appropriate area on Blackboard by no later than the beginning of class on Saturday, February 25, and make sure you also have access to the files during lab on that day. The two files must be sumitted separately in the same area on BlackBoard, not in a ZIP file. You must submit both at once, not separately.
We will use them again in future exercises both in lab and in homework. When a homework assignment is built upon in in the very next lab session, as is the case for this one, the deadline is absolute; it cannot be submitted late. In case Blackboard 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.