(forthcoming
from Allyn and Bacon)
David A. Patterson, Ph.D.
The University of Tennessee
Randall E. Basham, Ph.D.
The
University of Texas as Arlington
Data Analysis with Spreadsheets clearly describes and illustrates the procedures
for conducting data analysis with spreadsheets, the most commonly available
software for social work students and professionals. Readers are not just
told about data analysis; they are shown how to do the analysis with videos
available on the accompanying CD-ROM. Social workers
and human services professionals are increasingly called upon to evaluate
the outcomes of their practice, to measure change of their clients over time,
to determine if one type of approach to services delivery in an agency is
better than another, to respond to requests for evaluative data from managed
care companies and other funding sources. Unfortunately,
one of the great deficits in the educational preparation of students in social
work and the human services is that they are commonly taught limited data
analysis skills using conventional statistical analysis software such as
SPSS. The problem with this approach is that social service and health care
agencies seldom have expensive statistical software such as SPSS available
for agency research projects and program evaluation. Conversely, most personal
computers have installed spreadsheet software.
We wrote Data Analysis with Spreadsheets
and created the accompanying CD-ROM as a comprehensive guide to the application
of spreadsheets to commonly encountered data analysis problems in practice
evaluation, statistical analysis, and program evaluation.
In this text and on the CD-ROM, we demonstrate the use of spreadsheets
in the full-spectrum of data analysis skills requisite for the evaluation
of practice, agency-based research, and program evaluation. We invite you
to review the Table of Contents below.
Chapter
I. AN INTRODUCTION
TO SPREADSHEETS
Spreadsheets – First
Facts
The Brief History
of Spreadsheets
Why use spreadsheets
for data analysis in the social services?
Spreadsheet Basics
Using Formulas and
Functions
Simple Formulas
Operators
in Formulas
Excel Functions
Advanced Tools for
Understanding Data
A Comparison of Spreadsheets
to Statistical Analysis Software
The Paradox of Spreadsheets
and Statistical Software in Social Service Practice
Chapter Summary
References
The Many Paths of
Data Acquisition
Levels of Measurement
Data Coding
Comment Windows
Text Boxes
Codebook Sheets
Basic Data Entry
Creating Data Entry
Forms
Downloading Data
Format
Web-based Data Retrieval
Excel
Web Query
Relating to Database
Software
Saving What You Have
and Using What You Need
Chapter Summary
References
Samples: First Facts
Simple Random Samples
Systematic Sampling
Stratified Sampling
Random Assignment
Chapter Summary
References
Types of Problem Data
Detecting Data Errors
Eyeballing the Data
Auto Filter
Duplicate Detection
Pivot Table Duplicate Detection
Contingency Cleaning
Missing Data: Its Detection and Handling
Detecting Missing
Data
Handling Missing Data
Listwise Deletion
Mean Substitution
Chapter Summary
References
V. FREQUENCY
DISTRIBUTIONS AND HISTOGRAMS
Understanding the Distribution
of Values
Normally Distributed Data
Absolute, Relative and Cumulative Frequencies
Frequency Distributions
Production of Frequency Distributions with Spreadsheets
Producing Frequency Distributions in Excel
Using the Frequency Function
Using the Pivot Table Option
Producing Histograms
Using the Chart Wizard
Using the Data Analysis Histogram Option
Chapter Summary
References
VI. DESCRIPTIVE STATISTICS
Measures of Central
Tendency
Mean
Mode
Median
Formulas for Measures
of Central Tendency
Measures of Dispersion
or Variability
Variance
Standard Deviation
Standard Error
Kurtosis
Skewness
Range
Maximum and Minimum Scores, Sum and Count
Confidence Level
Data Analysis Tools:
Descriptive Statistics
Producing Frequency
Polygrams
Chapter Summary
References
VII. STATISTICAL INFERENCE AND HYPOTHESIS TESTING
Normal Distributions
Probability Distributions
Skewness
Understanding Hypothesis
Testing
Statistical Significance
p-value
Type I and Type II Errors
Effect Size and Sample
Size Estimation
Chapter Summary
References
VIII.
BIVARIATE STATISTICS
Chi-square Test of
Independence
Observed Frequencies
Expected Frequencies
Chi-square value
p-value
t-Test
One-sample t-Test
t-Test for Two Independent Samples
Paired Samples t-Test (Dependent)
ANOVA
One-way Between-Groups
Two-way Between-Group
Post hoc Tests of
Means
Levine’s Test
Bartlett’s Test
Duncan’s Test
Tukey’s b Test
Chapter Summary
References
IX. Cross-Tabulation
Tables
Using the Pivot Tables
Wizard
Creating Two-way Tables
Creating Tables with Three Variables
Graphing Cross-tabulation
Tables
Chapter Summary
References
X. SINGLE SYSTEM DESIGN
Basic Elements of
Single System Design
Data Recording
Visual Display of Data
Advanced Designs
AB-Designs
ABAB-Designs
Dynamic Chart Production
Analyzing Change
Celeration Line
Regression Line
Moving Average
Autocorrelation
Single System Group
Designs
Standard Deviation Enhanced Line Graphs
Surface Plots
Chapter Summary
References
XI. CORRELATION AND REGRESSION
Pearson's r and R2
Computing Pearson’s r and R2
Bivariate Regressions
Regression
Two-variable Regression
Creating Scatterplots
Trendlines in Scatterplots
Interpreting Scatterplots
Ordinary Least Squares
(OLS) Regression
Computer OLS Regression
Interpreting Multivariate
Regression (OLS) Output in Excel
Creating a Control
Variable for Regression Analysis in Excel
Chapter Summary
References
XII. GRAPHICAL
REPRESENTATIONS OF PRACTICE INFORMATION
Thinking Visually
Graphical Representation
Tools for Graphical
Representation of Information
Spreadsheets
Data Selection Prior to Graphing
Matching Data to Type
of Graph
Pie Charts
Line Graphs
Area Graphs
3-D Area Graphs
Surface Plots
Enhancing Graphs
Spreadsheet Drawing
Tools
Ecomaps
Genograms
Chapter Summary
References