D. Nixon: CS 12 > Week 9

Home (CS 12) | Assignments | Tutorials | Quizzes & Exam | Other Info

Computer Science 12


Week 9




Friday, November 4, 2011



Assignment 9 (due next week)

  1. Excel assignment:

    2. Practice using VLookup as in this week's Excel tutorials. Practice first by duplicating one or more of the examples in the tutorials.

    Modify your old "invoice" file so that it contains a separate "Inventory" table (located further down in the spreadsheet), listing products together with their unit prices, quantity in stock, and the column indicating whether a given item is taxable. The new table should also contain at least a few more products besides the ones in the invoice. (The idea here is that the inventory table will contain information that stays the same for all buyers, whereas the invoice itself will vary from one buyer to another.) Then, in the original invoice table itself, replace the hard-coded numbers in the "unit price" column with VLookUp formulas which look up the unit price in the separate inventory table, using VLookUp. Likewise, replace the hard-coded T's and blanks in the "taxable" column of the original invoice table with VLookUp formulas which look up whether an item is taxable. Make sure the formulas are correctly draggable.

    Then modify your old "roster" file to contain an additional table which converts percentage scores to letter grades. Add a column to the main roster table for the letter grade, and use the separate table, with VLookup, to compute each student's letter grade, based on the final numeric score.

    When you are finished, upload both your "invoice" file and your "roster" file to the appropriate place on Blackboard. They must have filenames like the following:

    last-first-invoice.xlsx
    last-first-roster.xlsx

    (replacing "last" and "first" with your own last and first name).

  2. Practice using file transfer (via WinSCP) and a command-line interface:

    3. Upload your HTML pages and graphics onto your website, as instructed in this week's tutorials on WinSCP. Practice using both the Windows Command Prompt and Unix, as instructed in this week's tutorials on the Windows Command Prompt and Unix.

  3. Blog:

    4. Log in to the blog you created last week on Wordpress.com. On your dashboard, in the left column, under "Settings, click "General." Give your blog a site title and a tag line. (One of these can be your name, if you so choose.) Make sure you click "Save Changes," then view your blog itself to see the changes.

    Then go back to your dashboard and click on "Appearance" in the left hand column. Find and activate a theme which either supports "Widgets" or supports all of the following: "Categories" (or "category list"), "tags" (either "tag list" or "tag cloud"), and "Links" ("links list" or "blogroll"). After you've activated the theme, if it supports "Widgets," go back to the "Appearance" page and select "Widgets." Then, on the "Widgets" page, give your blog "Categories" (or "category list"), "tags" (either "tag list" or "tag cloud"), and "Links" ("links list" or "blogroll"), if it does not already have these.

    "Categories" are a way of creating a table of contents for your blog, while "tags" are a way of creating an index. Having both of them on a side panel of your blog will make it easier for users to find posts on your blog. They will also improve your site's Google rankings, by providing relevant links to your blog posts.

    On your dashboard, click "Posts" in the left column. Then, below "Posts" in the left hand column, click "Categories." Add a new category "Wordpress" for your posts about Wordpress itself. Add at least one other category on any topic you choose, which will include the first post you made yesterday. A category name should usually consist of just one or two words.

    Then click "Posts" and edit all your posts to select at least one category (not including "Uncategorized") and give them at least three or four tags referring to sub-topics discussed in your post. Tag names, like category names, should each consist of just one or two words.

    When finished, view your blog. Your categories and tags should be listed on a panel separate from your posts. Your categories and tags for each post will also be listed beneath the post itself. When you click on a category or tag on the separate panel, it will take it to a page of your own posts with that category or tag. On the other hand, when you click on a category or tag beneath one of your posts, it will take you to a list of posts, not just by you but by all Wordpress.com users, with that category or tag.

    Let's now give your blog a links list or blogroll. On your dashboard, click "Links" in the left hand column. Add a link to the Queens College Computer Science Department home page. Add at least two other links to websites on topics of interest to you. Then view your blog and make sure your links work.

    When finished, copy and paste the URL of your blog (http://username.wordpress.com/ replacing "username" with your actual Wordpress username) into the "notes" area of the relevant page on Blackboard. (Do not submit a file.)

  4. Paper:

    5. Finish writing your paper. It should be six to eight pages long, double spaced, with a font size anywhere between 12 and 15. The final version of the paper must have a table of contents at the beginning, properly formatted footnotes (which will place themselves automatically at the bottom of the relavant page), citations, a bibliography, and at least three other sections whose titles will appear in the automatically generated table of contents at the beginning. The paper must be an original paper, not plagiarized. (Warning: BlackBoard includes the ability to search automatically for possible plagiarism.)

    The paper is due two weeks from now. The paper must have the following filename:

    where "last" and "first" should be replaced by your own last name and and first name, respectively.


To all information for Week 9  |  To all homework assignments



Tutorials on Excel topics covered this week

On Excel's VLOOKUP function:


To all information for Week 9  |  To all tutorials about Excel



Tutorials on miscellaneous topics covered this week

Logging on to a Unix system via WinSCP:

To upload a file via WinSCP, once you are logged in: (1) On the left side, navigate to the directory where your files are, on your local machine. (2) On the right side, go into the  public_html  directory, which is where you must put your files in order for them to be visible on your website. (3) On the left side, select the files you want to copy. After highlighting them, select "Copy" from the "Files" menu. (4) Click on the right side, then select "Paste" from the "Files" menu and respond appropriately to subsequent prompts. (5) Once you've uploaded all your files, view them at a URL like the following, to make sure you've uploaded them correctly:

http://cs12.cs.qc.cuny.edu/~yourusername/

replacing " yourusername " with your actual username. Be sure to include the tilde (" ~ ") just before your username.

Brief introduction to Unix:

In WinSCP, you can access the Unix command line (shell) by selecting "Open Terminal" from the "Commands" menu, once you are logged in to  cs12.cs.qc.cuny.edu .  (You will be prompted to log in again.) You do NOT need to do this in order to uploaded your files, which can be done easily via the GUI interface. Nevertheless, once you have successfully uploaded (and viewed) your files, you should take the opportunity to learn a little about Unix.

File permissions and  chmod  on a Unix/Linux machine:


To all information for Week 9  |  To all tutorials about miscellaneous topics



Study guide for next week's quiz

The November 4 quiz will be an on-paper quiz.

There will probably be some questions about Excel's VLookup function. The questions may include fill-in-the-blanks and/or multiple choice questions and/or a problem in which you are given a picture of a spreadsheet and asked to write an appropriate formula to go into some specified cell.

There will probably be a spreadsheet problem involving one or more of the previously-covered Excel functions, including LARGE, PMT, FV, PV, SUM, COUNT, AVERAGE, MIN, MAX, the date functions (DATE, TODAY, DAYS360, and simple difference in days), and especially IF, AND, OR, COUNTIF, SUMIF, and AVERAGEIF.

There will probably be some fill-in-the-blanks and/or multiple choice questions about WinSCP and Unix. Make sure you know how to use the Unix commands pwd, ls (including ls -l and ls -a) , cd, mkdir, and cp. (Note that the cd and mkdir commands are pretty much the same in Unix and the Windows Command Prompt, and note that ls in Unix is roughly equivalent to dir in the Windows Command Prompt.) Make sure you know what a "directory" is, and what a "pathname" is.

There will probably be some fill-in-the-blanks and/or multiple choice questions about Wordpress blogs. Be familiar with the use of themes, and be familiar with the use of categories, tags, and links lists.


To all information for Week 9  |  To all quiz and exam study guides

D. Nixon: CS 12 > Week 9