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

No Class Today (Sunday Apr 23)

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)

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.

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.

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

• Statistical Graphics
  
• Graphical Methods for Categorical Data
  
  
  
  • Mosaic displays
    
  • Fourfold displays
    
  • Visual and Conceptual Models for Categorical Data
    
  • Other methods for categorical data
    
  
  
• SAS/GRAPH meets the Logo Turtle
  
• APL2STAT
  
• York Statistical Consulting Service
  
• Other links
  
• To contact me
  

Visualizing Categorical Data
Statistical & Graphics Resources
Statistical Consulting Service
Statistics on the Web
SAS macros for Statistical Graphics
More SAS macros
Data Visualization Gallery

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.



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.

• Outline of The SAS System for Statistical Graphics, 1st Edition
  
• Statistical graphics for multivariate data (SUGI16 paper).
  
• Macro Programs from The SAS System for Statistical Graphics
  
  
• More SAS Macros for Statistical Graphics
  
  
• Designer Graphics - Anatomy of Some SAS Macros for Statistical Graphics
  

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.

• VCD web site || VCD Sampler Visualizing Categorical Data: Data, Stories, and Pictures (SUGI 25 paper)
  
• SUGI17 HTML reprint
  
• SUGI 17 PostScript reprint (sugi17.ps.gz) [51K]
  
• HTML Course Notes, Categorical Data Analysis with Graphics
  

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.

• Try an online web applet of Mosaic displays with sample data or with your own data [requires JavaScript-capable browser].
  
• JASA article reprint request
  
• ASA '92 paper (HTML)
  
  
• Extending Mosaic Displays ..., Data Visualization in Statistics Conference, July, 98 (HTML) || PDF version
  
  
• Mosaics User's Guide || PDF version
  
  
• mosaics.sas [29K]
  || Sample (mosademo.sas) [1K] || mosaics.tar.gz [50K]
  || mosaics.zip [54K]
  
  

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..

• American Statistician article reprint request
  
• PostScript preprint text (grmodel.ps.gz) [21K]
  

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.

• preprint request
  
• PostScript preprint (4fold.ps.gz) [86K]
  
• fourfold.sas [13K]
  || Sample (fourdemo.sas) [1K]
  

Other methods for categorical data

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

• Effects plots for PROC CATMOD (catplot.sas)
  || CATPLOT example (catdemo.sas)
  
  
• Influence plot for logistic regression (inflogis.sas)
  
  
• MLEs for incomplete n-way contingency tables (missrc.sas)
  
  
• Cohen's Kappa [PROC IML] (kappa.sas)
  
  
• Ord plots for discrete distributions (ordplot.sas)
  || Ord plot sample (orddemo.sas)
  
  
• Poissonness plot (poisplot.sas)
  || Poissonness plot sample (poisdemo.sas)
  
  
• Sieve diagrams (sieve.sas)
  || Sieve diagram sample (sievedemo.sas)
  
  
  

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.

• JCGS/SMR article reprint request
  
• readme file for APL2STAT [11K]
  
• APL2STAT User's Guide [69K]
  
• General ftp to APL2STAT directory
  
• Updates directory
  
  
• APL2STAT v2.0 files for IBM APL2 or TRYAPL2 (a2stry20.zip) [469K]
  
• APL2STAT v2.0 .atf (source) files for IBM APL2 (a2satf20.zip) [144K]
  
• APL2 matrix library for IBM APL2 (matrix.zip) [41K]
  
  

York University Statistical Consulting Service

• Statistical Consulting Service
  
• Data Visualization Discussion Group
  

Other Links

• Statistics and Statistical Graphics Resources.
  A growing collection of pointers to useful resources in statistical computing, graphics, teaching, and data on the net.
  
• SAS Institute WWW server
  
• Data Visualization Sites and a related visualization & image collection