Business Statistics with EXCEL

Some extraordinary data sources on the web:

The Data Ferret gives access to Census and CDC data
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.
http://www.graphpad.com/articles/interpret/principles/stat_principles.htm
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 videotutorials from http://www.datapigtechnologies.com
Here's a link directly to the EXCEL videotutorials at Datapig
http://contextures.com/tiptech.html

http://www.ozgrid.com

Some Odd Issues with EXCEL

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.

Table of Video Tutorials:

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 large files (3-60 MB). Although they will download rapidly in the school system, downloading by modem at home would be impractical. You can borrow earphones to listen while playing them in SSB103 lab. 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.
Some machines on campus have zipdisks or CD writers which would allow you to download and copy for home use. You could also download files to a USB flash memory device for portability.
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.avi

Print EXCEL file to one page prn1page.avi

video on importing text data and parsing. import.avi

Sampling from airplane empty seats data: sample.avi

Paste special transpose to transpose data from rows to columns. transpose.avi

sorting rows sortht.avi

adding in addins/tools addins.avi

descriptive statistics procedure dscstats.avi

histograms histgram.avi

2-sample F test for unequal variances ftestvar.avi

t-test 2 sample treating variances as equal ttst2smp.avi

1-sample ttest 1samplet.avi

conversion of survey text to logical values

paired values ttest pairedt.avi

1-factor ANOVA - with audio anova1V.avi

2-factor ANOVA with replication- with audio anova2v.avi

Interaction in 2-way ANOVA interact.avi

Copying text out of a pdf pdftxt.avi

pasting text into EXCEL pastechi.avi

Modifying (expand/contract) the Chi2 sheet expchi.avi

pasting in new data for the Chi2 pastenew.avi

chart with trendline trendline.avi

simple linear regression simpregr.avi

multiple regression yr1mregr.avi

Use of Winzip to compress files winzip.avi

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.datNCHRBcodebook (pdf)

Use of Winzip to compress large files winzip.avi

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

Using EXCEL Pivot Tables for Crosstabulation
Completed sheet with YRBS2001 data imported and parsed

YRBS2001

Yrbs Download yrb03dl.avi dlyrb1.avi

Import and parse all of YRBSS 2001or 2003 into EXCEL yrb3pars.avi importxl.avi

add labels to tops of columns for field names yrb3lbl.avi lblontop.avi

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

Use Pivot table to select crosstab variables
(Description handout)     pivot03.avi pivottbl.avi

Calculate the percent of males and females who smoke    pctsmk03.avi pcsmoke.avi

What would you expect if there were no relationship (Ho true)     expect03.avi expect.avi

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

Other Crosstabs modify Pivot tables to use a different field-     pivchvar.avi modfield.avi

Other Crosstabs -adjust tables for new dimensions-- the "auto Chi-square"     newchi.avi

Other summary measures- smoking, gender and average weight     pivchvar.avi smkavgwt.avi

Stock Return time Series and Market Beta

Stock returns--download IBM and S&P500 from http://finance.yahoo.com/
dl-sp-ibm.avi

Stock returns--Calculating a Market Beta for IBM abibmsp.avi

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.avi and import2.avi

Crosstab Query in ACCESS (yrbs1999 example) crosstab.exe

Table of Video Tutorials: 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 large files (3-60 MB). Although they will download rapidly in the school system, downloading by modem at home would be impractical. You can borrow earphones to listen while playing them in SSB103 lab. 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. Some machines on campus have zipdisks or CD writers which would allow you to download and copy for home use. You could also download files to a USB flash memory device for portability. 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.avi
Print EXCEL file to one page	prn1page.avi
video on importing text data and parsing.	import.avi
Sampling from airplane empty seats data:	sample.avi
Paste special transpose to transpose data from rows to columns.	transpose.avi
sorting rows	sortht.avi
adding in addins/tools	addins.avi
descriptive statistics procedure	dscstats.avi
histograms	histgram.avi
2-sample F test for unequal variances	ftestvar.avi
t-test 2 sample treating variances as equal	ttst2smp.avi
1-sample ttest	1samplet.avi
conversion of survey text to logical values
paired values ttest	pairedt.avi




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

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.datNCHRBcodebook (pdf)
Use of Winzip to compress large files	winzip.avi
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.avi

Using EXCEL Pivot Tables for Crosstabulation	Completed sheet with YRBS2001 data imported and parsed
		YRBS2001
Yrbs Download	yrb03dl.avi	dlyrb1.avi
Import and parse all of YRBSS 2001or 2003 into EXCEL	yrb3pars.avi	importxl.avi
add labels to tops of columns for field names	yrb3lbl.avi	lblontop.avi
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.avi
Use Pivot table to select crosstab variables (Description handout)	pivot03.avi	pivottbl.avi
Calculate the percent of males and females who smoke	pctsmk03.avi	pcsmoke.avi
What would you expect if there were no relationship (Ho true)	expect03.avi	expect.avi
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.avi	chi2calc.avi
Other Crosstabs modify Pivot tables to use a different field-	pivchvar.avi	modfield.avi
Other Crosstabs -adjust tables for new dimensions-- the "auto Chi-square"	newchi.avi
Other summary measures- smoking, gender and average weight	pivchvar.avi	smkavgwt.avi
Stock Return time Series and Market Beta
Stock returns--download IBM and S&P500 from http://finance.yahoo.com/	dl-sp-ibm.avi
Stock returns--Calculating a Market Beta for IBM	abibmsp.avi
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.avi and import2.avi
Crosstab Query in ACCESS (yrbs1999 example)	crosstab.exe

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

last modified May 13, 2005
return to home page

Some Odd Issues with EXCEL	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.