Some Odd Issues with EXCEL 
workaround 

EXCEL may remove commas on import of commadelimited 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"try downloading the Excel Ribbon mapping workbook. 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 (19972003) 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=941b34703ae94aee8f43c6bb74cd1466&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 StatisticsComparison 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: Ttests 

Male & Female GradesDescriptive Statistics (10:25)  dscstatsg 
Histograms (8:53)  histsg 
(unpaired) 2sample ttest (5:04)  2smpltsg 
Ttest on differences between pairs (11:22)  difftsg 
The paired Sample Ttest 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... 

Download and parsing YRBSS2005 dataset  y5parse 
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 ministereo
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 managementhow 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  
adding in addins/tools  addins  
descriptive statistics procedure  dscstats  
histograms  histgram  
2sample F test for unequal variances  ftestvar  
ttest 2 sample treating variances as equal  ttst2smp  
1sample ttest  1samplet  
conversion of survey text to logical values  
paired values ttest  pairedt  
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 

1factor ANOVA  with audio  anova1V  
2factor ANOVA with replication with audio  anova2v  
Interaction in 2way 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  
Using EXCEL Pivot Tables for Crosstabulation 

Simpson's Paradox 
Some other useful links on this topic:http://plato.stanford.edu/entries/paradoxsimpson/http://www.google.com/search?hl=en&q=simpson%27s+paradox http://core.ecu.edu/psyc/wuenschk/StatHelp/ReversalParadox.txt 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 Sexbias Data analysis by pivottable and Chisquare  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 Chi^{2} Calculation and interpretation  Video berkchi2.html  
Youth Risk Behavior Surveys 

Completed sheet with YRBS2001 data imported and parsed 
YRBS2001 

Yrbs Download  yrb03dl  dlyrb1 
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 regressionconverting 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 mjgrdgrf adding graphs 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 Chisquare"  newchi  
Other summary measures smoking, gender and average weight  pivchvar  smkavgwt 
Stock Return time Series and Market Beta 

Stock returnsdownload IBM and S&P500 from http://finance.yahoo.com/ 
dlspibm 

Stock returnsCalculating a Market Beta for IBM  abibmsp  
Example Sheet IBM, AB and S&P  SPbudibm.xls  
Use of Access to predigest data for EXCEL analysis(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  
Email: ba3300 @ budbanis,com
Campus office CCB 230
Oncampus phone 3145166136
Offcampus 6363944950
return to home page