## Some extraordinary data sources on the web:

www.fedstats.gov  gives links to a large variety of government sites with statistical data
Statistics.com is an extraordinary resource for tools and data
The National Center for Health Statistics at CDC is a great central source on health statistics and microdata
You may have a use for Math Programming and EXCEL addins at http://www.mathtools.net/
Here is a web site I have found helpful.
Here is a database I found which has survey data.
www.netcraft.com/survey/reports/

# EXCEL tips, tricks and tutorials:

Tutorials on pivottables and other EXCEL
http://www.mrexcel.com/archive/Pivot/
http://www.ozgrid.com/Excel/PivotTables/ExCreatePiv1.htm
http://office.microsoft.com/en-us/assistance/HA010346331033.aspx
Excellent EXCEL Videotutorials from DataPigTechnologies
convenience copy of the course CD at statisticsvideos.com
http://contextures.com/tiptech.html

http://www.ozgrid.com

# workaround

EXCEL may remove commas on import of comma-delimited data replace commas in source with another delimiter.
paste and parse in one step with the comma delimiter set in the import wizard
Tdist won't work with negative T scores and doesn't say why use the abs() function to provide the referenced number as a positive.

# Simple Simulations of Statistical Concepts

ANOVA uses averaging to selectively eliminate variance due to different causes, allowing us to simultaneously measure the effects of different factors.
Anova is used when the dependent variable is continuous, but the factors are described as discrete categories
anovarat.xls has macros in it because it has a dropdown list used to set the level of averaging. set security to medium and allow EXCEL to enable the macros.
Multiple Regression. The Varsorc model visualizes the concept of multiple regression. We are able to  simultaneously detect effects of several variables. by mathematically correcting for all but the one of interest. varsorc.xls has macros in it because it has spin buttons. set security to medium and allow EXCEL to enable the macros.

## HEY!! In Office 2007, where did the EXCEL menus go?

How to find EXCEL 2003 commands in EXCEL 2007 "Ribbons"
I recommend clicking on the OFFICE icon at the top left of EXCEL 2007, click EXCEL OPTIONS (a button down at the bottom) and fix whatever needs fixing to get access to what you need, such as "Developer tab." Change the SaveAs default to old (1997-2003) EXCEL so you can still work on the product files on older versions of EXCEL. In case you have to, you might be able to find file converters at microsoft.com. They are also promising to someday make their file formats open source. Uncommon fonts may also wreak havoc as you try to work on a file across machines. Change default font theme under "page layout." I know Times New Roman and Arial are uninspiring, but they are more likely to be on your other machine. After I get a feel for what needs to be fixed in the School's setup of EXCEL, I'll produce some guidance and videos on how to fix things, so you can relate my existing videotutorials to EXCEL 2007.
Microsoft has a compatibility pack for installation in older versions of OFFICE so you can read and write the new file formats. You need to install all the updates first. http://www.microsoft.com/downloads/details.aspx?FamilyId=941b3470-3ae9-4aee-8f43-c6bb74cd1466&displaylang=en
if you can't use the link, then you can probably find it at the microsoft office site or google it.

## Videotutorials on Statistics in EXCEL 2007

### Exercise 1 Sampling and the Central Limit Theorem

--Exercise 1, An experiment on sampling statistics, the Central Limit Theorem and 95% Confidence Intervals
Random Sampling Procedure (6:05) airsampl
Repetitive Sampling with Macros (10:41) samplmacro
Sample Statistics Means, StDev, SEM and 95% CI (11:40) samplstats
Descriptive Statistics-Comparison of Sample Means to Population (11:11) dscstats
Histograms of population vs. sample means (12:47) CLThistograms
Use a textbox for comments (7:18) textbox
Set up to print to one Page (6:41) cltprt1p

### Exercise 2: T-tests

Male & Female Grades--Descriptive Statistics (10:25) dscstatsg
Histograms (8:53) histsg
(unpaired) 2-sample t-test (5:04) 2smpltsg
T-test on differences between pairs (11:22) difftsg
The paired Sample T-test in EXCEL (3:06) pairtsg
Scatter Charts and Correlation (6:51) correlsg
Text box and setup to print to 1 page (5:23) prn1psg
Exercise 3: ANOVA
One -way Anova on sales pricing and volumes (10:53) anova1way
Two -way Anova on sales pricing, shelf space and volumes (6:01) anova2way
Two -way Anova Interaction of sales pricing and shelf space on volumes (4:52) interact

Final Project- pivottables on the YRBSS2005 data in EXCEL 2007
You might also want to look at these if you are doing exercise 5 & 6 with EXCEL 2007. pivottables work a little differently in EXCEL2007, and the file formats don't convert backwards, so you can't easily work across versions. More on this as I study it further...
Making a pivot table in EXCEL 2007 y5pivot
Saving the file with a pivottable (incompatible with earlier versions) y5pivsave

# Table of Video Tutorials in Earlier Versions of EXCEL:

## Some of these are EXE's  that include the viewer. In that case, you may have to save to the hard drive and run using window explorer or Start/ Run

These are FLASH format video files, small enough to download from home (if over the phone, eventually!).  You can borrow earphones to listen while playing them in School labs. Double click on the speaker down in the taskbar to remove muting and increase volume. plug the earphone into the green connector on the back of the computer. It uses a mini-stereo plug.
For your convenience, these are widely available from students who have copied the course CD's.
You have permission to copy, use and distribute all these videos for personal and nonprofit educational use.

Use Windows Explorer rather than "open" for file management--how to copy vs. moving files
rtclkcpy
Print EXCEL file to one page prn1page
video on importing text data and parsing. import
Sampling from airplane empty seats data:  samplek
Paste special transpose to transpose data from rows to columns. transpose
sorting rows sortht
descriptive statistics procedure dscstats
histograms histgram
2-sample F test for unequal variances ftestvar
t-test 2 sample treating variances as equal ttst2smp
1-sample ttest 1samplet
conversion of survey text to logical values
paired values ttest pairedt
• Other videos on excel analysis of characteristics of the distribution of sample means:

• Finished spreadsheet results show Central Limit Theorem on distribution of sample means.

confidence limits on estimates of the population mean compared to the actual value
Comparison of the distribution of original values to the distribution of sample means

1-factor ANOVA - with audio  anova1V
2-factor ANOVA with replication- with audio  anova2v
Interaction in 2-way ANOVA  interact
Copying text out of a pdf   pdftxt
pasting text into EXCEL pastechi
Modifying (expand/contract) the Chi2 sheet expchi
pasting in new data for the Chi2 pastenew
chart with trendline trendline
simple linear regression simpregr
multiple regression yr1mregr
Use of Winzip to compress files winzip

## Data for the YRBSS 2001 & 2003 are on this disk, as well as NCHRBS 1995

The videos below are with EXCEL XP and the YRBS 2001 and YRBS 2003 data as indicated. New data is released every two years at the CDC website. There is also a dataset there for the National College Health Risk Behavior (NCHRBS) conducted in 1995.
There may be a new version of the NCHRBS in the near future, so it would be good to check the CDC websites from time to time. Data from adult risk behavior surveys and census data are available at the dataFerret site. For convenience, ASCII data and descriptions available now are linked here and are on this disk so you don't have to download it:
YRBS01.dat  yrb01codebook (pdf)
YRBS03.dat  yrb03codebook (pdf) YRB03powerpointslides
NCHRBS95.dat  NCHRBcodebook (pdf)
Use of Winzip to compress large files winzip
more instructions and links to WINZIP & FTP install on this disk
screen captures of YRBS2001 parsing in EXCEL Screen Captures of YRBS2003 parsing in EXCEL
multiple regression (description handout) yr1mregr

## Some other useful links on this topic:

http://repository.upenn.edu/cgi/viewcontent.cgi?article=1014&context=wharton_research_scholars
http://wolfweb.unr.edu/homepage/jerryj/NNN/Aggregates.pdf
U. Cal. Berkeley Graduate School Admission Sex-bias Data analysis by pivottable and Chi-square The partial dataset was obtained from Gerstman B.B. (2000) Data Analysis with Epi Info, Binary Outcome, Stratified Analysis on the web at http://www.sjsu.edu/faculty/gerstman/EpiInfo/stratified.htm
Screencaptures showing formulas Exposition.doc
Dataset in EXCEL sexbias2.xls
Setting up a Pivottable Video berkpivt.html
calculating percents of admissions Video berkpct.html
flexible Chi2 Calculation and interpretation Video berkchi2.html

## Youth Risk Behavior Surveys

Completed sheet with YRBS2001 data imported and parsed

## YRBS2001

Import and parse  all of YRBSS 2001or 2003 into EXCEL   yrb3pars importxl
add labels to tops of columns for field names  y3lbltop lblontop
Prepare EXCEL file of YRBS '03 data for multiple regression--converting to actual ages,height in inches, weight in pounds and eliminating empty data fields regrprep
Use Pivot table to select crosstab variables
(Description handout)
pivot03 pivottbl
Current Marijuana Use and grades, with page variables.
"Do people smoke pot because they are stupid, or are they stupid because they smoke pot?"
mjgrd1       setting up the pivottable
mjgrdpct    calculating percents by rows and columns
mjgrdpag  using the page variables
More complex examples of pivottables with grouping of categories and more automated data extraction BA2000 website Spreadsheet models in Management Science
Calculate the percent of males and females who smoke    pctsmk03 pcsmoke
What would you expect if there were no relationship (Ho true)     expect03 expect
Chi2 value =Does the Observation agree with your expectation? (Description handout
Updated versions of these are on the web.
Printable handout as WORD document Chi2.doc
03smkch2 chi2calc
Other Crosstabs modify Pivot tables to use a different field-     pivchvar modfield
Other Crosstabs -adjust tables for new dimensions-- the "auto Chi-square"     newchi
Other summary measures- smoking, gender and average weight     pivchvar smkavgwt

## Stock Return time Series and Market Beta

dl-sp-ibm
Stock returns--Calculating a Market Beta for IBM abibmsp
Example Sheet IBM, AB and S&P SPbudibm.xls

(an alternative to EXCEL Pivot Tables)
parsing YRBSS data into Access and Excel  (ACCESS with yrbs1999 example) import wizard in EXCEL is very similar  imptaccs  and import2
Crosstab Query in ACCESS (yrbs1999 example)  crosstab

E-mail: ba3300 @ budbanis,com
Campus office CCB 230
On-campus phone 314-516-6136
Off-campus 636-394-4950