cs12

Saturday, April 29, 2006

Excel Exam

In this exam, you will create an excel spreadsheet to manage a Grade Book.

1) Start Excel. Save the Workbook as Exam.xls.

2) Identify yourself. Select cell A13 and enter your name.

3) Formatting. In cell A1, enter the words "Grade Book Examination." Format the text, making it Bold, Italics, Fontface "Courier New" and Font Size 40, color Red. Merge and Center fields A1 through E1.

4) Change the name of worksheet 1 to Spring '06.

5) Enter the Column Names.
The columns should be called First, Last, SSN, Midterm, Final, HW, Bonus, Total, Letter Grade. Skip a cell between SSN and Midterm.
Bold these column names.

6) Enter Data for 3 students: (You can make up alternate Names and SSNs.)
John Doe, 111-22-4321, 74, 89, No
Jane Smith, 222-12-1354, 56, 100, Yes
Sarah O'Conner, 123-66-2352, 86, 96, No

7) Calculate the HW Bonus.
In cell A12, type HW Bonus, and bold it. In cell B12, type the HW Bonus (in this case, 10).
For the first student, calculate the HW bonus (either 0 points or the points, depending on whether homework was done or not done.)
Apply the same formula to the next two students by dragging.

8) Calculate the Total Grade. That is, the average of the midterm and final, plus the HW bonus. Apply to all students.

9) Now we calculate the letter grade, using VLOOKUP.
0 to 59 = F
60 to 69 = D
70 to 79 = C
80 to 89 = B
90 to 100 = A

Create the table for this lower in the spreadsheet, and use VLOOKUP for the first student. Apply to all students.

10) Sort the students by Last Name.

11) Create a bar chart by Last Name and Final Grade. Delete the legend.

ssns:

http://www.snopes.com/business/taxes/woolworth.asp

More recent syllabus

Sunday, April 23, 2006

No Class Today (Sunday Apr 23)

Monday, April 10, 2006

Greades for the First Two Exams

I've finally finished grading the first two exams.

The scores on the first exam (DOS/Windows) ranged from 75 to 100.
The scores on the second exam ranged from 91 to 100.

If you would like to know your score, send me an email and I will respond with your score. (joshwaxman [at} gmail dot com)

Sunday, April 09, 2006

Sample Excel Exam

In this exam, you will create an excel spreadsheet to manage a Grade Book.

1) Start Excel. Save the Workbook as Exam.xls. Start recording a macro, and name that Macro Exam.

Solution:
Start/Run/Excel
Ctrl-S
Exam.xls and press Enter

Not graded: Tools/Macro/Record New Macro
Type Exam and press Enter

2) Identify yourself. Select cell A13 and enter your name.

3) Formatting. In cell A1, enter Grade Book. Format the text, making it Bold, Italics, Fontface "Courier New" and Font Size 20, color Red. Merge and Center fields A1 through E1.

4) Change the name of worksheet 1 to Spring '06.

Right-click on the "Sheet 1" Tab at the bottom of the Screen. Choose rename from the pop-up menu.

5) Enter the Column Names.
The columns should be called First, Last, SSN, Midterm, Final, HW, Bonus, Total, Letter Grade. Skip a cell between SSN and Midterm.
Bold these column names.

6) Enter Data for 3 students: (You can make up alternate Names and SSNs.)
John Doe, 111-22-4321, 74, 89, No
Jane Smith, 222-12-1354, 56, 100, Yes
Sarah O'Conner, 123-66-2352, 86, 96, No

7) Calculate the HW Bonus.
In cell A12, type HW Bonus, and bold it. In cell B12, type the HW Bonus (in this case, 10).
For the first student, calculate the HW bonus (either 0 points or the points, depending on whether homework was done or not done.)
Apply the same formula to the next two students by dragging.

Solution: =IF(G3="Yes", $B$12, 0)
Drag the formula to the two cells below

8) Calculate the Total Grade. That is, the average of the midterm and final, plus the HW bonus. Apply to all students.

Solution: =average(E3, F3) + H3. Click and drag to apply formula.

9) Now we calculate the letter grade, using VLOOKUP.
0 to 59 = F
60 to 69 = D
70 to 79 = C
80 to 89 = B
90 to 100 = A

Create the table for this lower in the spreadsheet, and use VLOOKUP for the first student. Apply to all students.

Solution:
The first column should be the lower bound for getting the grade, the second column should be the letter grade. Assuming you placed this table from K18 to L22, the formula should be:
=VLOOKUP(I3,K18:L22,2), but then change the table references to absolute with F4.

10) Sort the students by Last Name.

Solution: Highlight the students, choose Data/Sort, and select which column to sort by.

11) Create a bar chart by Last Name and Final Grade. Delete the legend.

Solution: Select the Last and Total columns, click on the chart icon. Click finish. select the legend and press delete.

12) Stop recording the macro. Alt-F8, Edit, and email me the text of the macro.

Wednesday, April 05, 2006

About HTML

Here is an html tutorial, with information about many html tags.

The tags I mentioned in class, all which should be enclosed in pointy brackets (less than and greater than signs):
HTML and /HTML to begin and end your HTML page.
HEAD and /HEAD
BODY and /BODY
P for new paragraph
BR for a line break
HR for a horizontal rule
IMG for an image
A for a hyperlink
OL for ordered list
UL for unordered list
LI for list item
H1, H2, H3, and H4 for text sizes
B for bold
I for italics

try to include some of these elements in the HTML page you create in notepad.

Sunday, April 02, 2006

Word Exam

For the purposes of this exam, you will create several files.

1) Start Word. Type in the following text, or else copy and paste it from the webpage. Change [Your Name Here] to your name:
Dear Resident:

The followinged people have won the lotterie, and we would like to extende our congratulations to them.

Hooray for them!

Sincerely,
[Your Name Here]

Save the file as Test2.doc.

2) Change the font of "Sincerely" to be Courier point size 14. Italicize the word "won." Make the font color for "Hooray for them!" green and the background color of these words red.

3) Run spell-check to correct any errors in the document.

4) Insert a page number for the document.

5) Add a page header, and type in Courier Bold, Centered, the words "Lottery Commission"

6) Change the top and bottom margins of the page to be .25 inches.

7) Insert a Table before the sentence "Hooray for them." The table should be 3 rows and two columns. Make up the names of 2 lottery winns. Make the first row the title of the columns (Name and Amount Won). For the remaining 2 rows: In the first column enter the lottery winner's name and in the second column enter the winnings for that person.

8) Save the file again.

9) Save the file as Test3.doc

10) Start Excel, Create a table with three columns. The first column should be called Title, the second FirstName and the third LastName. Make up information for 3 people. Save this file as merge2.xls. Close Excel.

11) Switch back to Word, and display the mail merge toolbar. Open a data source and choose the excel file you just created. Select Sheet 1.

12) Change Dear Resident to Dear [Title] [LastName],
where
[Title] and [LastName] are merge fields.

Save the file as Test3.doc

13) Actually perform the mail merge to a new document.

13) Save the file as Test4.doc. Call the proctor over to collect the files. Put the files on the disk, or else email them to me.

My Pets

Here are pictures of my pets




Contents











[Search]

Search Me!


If you can't find what you're looking for, you might want to try
this ht://Dig gateway I've set up to search my web.

Statistical Graphics


A number of my older papers are available through the links below as compressed PostScript files (.ps.gz).
You will need WinZip, or the Gnu zip utility, gunzip to decompress them, and a PostScript viewer (such as Ghostscript) to read or print them.



[ftp] Mac version of GZIP/GUNZIP (macgzip0.22.cpt.hqx)

SAS System for Statistical Graphics: macro programs




These items point to descriptions of SAS macro programs for statistical graphics related to my book,
The SAS System for Statistical Graphics, 1st Edition
and to web access to the most recent versions of these programs.


Graphical Methods for Categorical Data




The methods listed below (plus many more) are described in my
new book,
Visualizing Categorical Data.
See also my
Statistical Consulting Service short course, Categorical Data Analysis with Graphics .
A brief paper appears under the same title in the SAS User Group International Conference Proceedings, 1992, 17, 190-200.


Mosaic Displays



A mosaic display shows the frequencies in an n-way contingency table by nested rectangular regions
whose area is proportional to the frequency in a cell or marginal subtable.
The display uses color and shading to represent the sign and magnitude of standardized residuals from a specified loglinear model.
The method is described in "Mosaic displays for n-way contingency tables",Journal of the American Statistical Association, 1994, 89, 190-200 and in "Mosaic displays for loglinear models", American Statistical Association, Proceedings of the Statistical Graphics Section, 1992, 61-68.



Visual and Conceptual Models for Categorical Data


A number of graphical methods for categorical data depict cell frequencies by area or observation density. This paper describes and extends a physical, dynamic model for categorical data suggested by John Sall which provides a conceptual model for this visual representation of categorical data.
The paper appears in The American Statistician, May 1995.
There is also a related
abstract for the Conference on Visualization of
Categorical Data
in Cologne, Germany, May, 1995..


Fourfold Displays


The fourfold display is a graphic designed to display the frequencies in a 2 x 2, or 2 x 2 x K contingency table. Each cell is shown by a quarter circle, whose area is proportional to the cell count, in a way that depicts the odds ratio in each of K strata. Confidence rings for the odds ratio can be superimposed to provide a visual test of the hypothesis of no association in each stratum.


Other methods for categorical data


Most of these links are now superceded by
Visualizing Categorical Data.



APL2STAT is an implementation of an object-oriented system for statistical computation
and graphics in the APL2 language done jointly with John Fox.
The current version is implemented for IBM APL2 and the freeware TryAPL2 for PC-compatibles.


The principal ideas are described in "Using APL2 to create an object-oriented environment for statistical computation", Journal of Computational and Graphical Statistics, 1994, 3, 387-407, and
in "APL2 and APL2STAT", Sociological Methods and Research, 1995, 23, 47-56.



York University Statistical Consulting Service




Other Links