Statistical Fundamentals: Using Microsoft Excel for Univariate and Bivariate Analysis

628 Pages·2016·58.5 MB·English

by  Rovai A.P.

Table of Contents
Introduction
Constructs
Sampling
Measurement
1.2: Data Ethics
Principles
Statistical Reporting
1.3: Microsoft Excel Fundamentals
Cell Formatting
Cell Addressing
Entering Data
Entering Independent and Dependent Data
Entering Formulas
Tables
Pivot Tables
Generating Random Numbers
1.4: Summary of Key Concepts
1.5: Chapter 1 Review
2.1: Introduction to Descriptive Statistics
Sample Size
2.2: Measures of Central Tendency
Mean
Standard Error of the Mean
Median
Mode
2.3: Measures of Dispersion
Variance
Standard Deviation
Maximum & Minimum
Range
Interquartile Range
Percent Distribution
2.4: Measures of Shape
Coefficient of Skewness
Standard Error of Skewness
Standard Coefficient of Skewness
Coefficient of Kurtosis
Standard Error of Kurtosis
Standard Coefficient of Kurtosis
2.5: Measures of Relative Position
Percentile
Quartile
2.6: Normal Curve
The Normal Distribution
Transforming Raw Scores Into Standard Scores
Z-Score, N(0,1)
T-Score, N(50,10)
Normal Curve Equivalent (NCE) Score, N(50, 21.06)
Stanine Scores
Standardized Norm-Referenced Testing
2.7: Charts
Creating Charts
Line Chart
Area Chart
Column Chart
Bar Chart
Scatterplot
Histogram
Pie Chart
2.8: Analysis ToolPak and StatPlus Procedures
2.9: Summary of Key Concepts
2.10: Chapter 2 Review
3.1: Basic Concepts
Introduction
Probability
Parameter Estimation
Hypothesis Testing
Controlling Type I Error
3.2: Evaluating Test Assumptions
Introduction
Independence of Observations
Measurement Without Error
Normality
Absence of Extreme Outliers
Linearity
Homogeneity of Variance
Homoscedasticity
Sphericity
Absence of Restricted Range
Dealing with Deviations
3.3: Summary of Key Concepts
3.4: Chapter 3 Review
4.1: Hypothesis Test Overview
4.2: Goodness-of-Fit Tests
One-Sample t-Test
Chi-Square Goodness-of-Fit Test
Kolmogorov-Smirnov Test
4.3: Comparing Two Independent Samples
F-Test of Equality of Variance
Levene’s Test
Independent t-Test
Mann-Whitney U Test
4.4: Comparing Multiple Independent Samples
One-Way Between Subjects ANOVA
Kruskal-Wallis H Test
Post Hoc Multiple Comparison Tests
4.5: Comparing Two Dependent Samples
Dependent t-Test
Wilcoxon Matched-Pair Signed Ranks Test
Related Samples Sign Test
McNemar Test
4.6: Comparing Multiple Dependent Samples
One-Way Within Subjects ANOVA
Post Hoc Trend Analysis
Friedman Test
4.7: Association
Introduction
Pearson Product-Moment Correlation Test
Partial and Semipartial Correlation
Spearman Rank Order Correlation Test
Chi-Square Contingency Table Analysis
Phi (Φ), Cramér’s V , and Contingency Coefficient (CC)
Reliability Analysis
4.8: Linear Regression
Bivariate Linear Regression
4.9: Chapter 4 Review
5.1: The Research Manuscript
5.2: Research Report Organization
Front Matter
Introduction
Literature Review
Methodology
Results
Discussion
End Matter
5.3: Chapter 5 Review
Statistical Fundamentals

Using Microsoft Excel for
Univariate and Bivariate
Analysis

Third Edition

Alfred P. Rovai, Ph.D.

University College
Azusa Pacific University
Statistical Fundamentals: Using Microsoft Excel
for Univariate and Bivariate Analysis
Third Edition (Paperback)

Copyright ©2016 by Alfred P. Rovai
Published by Watertree Press LLC
PO Box 16763, Chesapeake, VA 23328
http://www.watertreepress.com
All rights reserved. Except for small excerpts for use in reviews, no part of this book
may be reproduced or transmitted in any form, by any means (electronic, photocopying,
recording, or otherwise) without the prior written permission of the publisher or author.
This book includes Microsoft Excel screenshots to illustrate the methods and procedures
described herein. Used with permission from Microsoft.
Trademarks
Microsoft® and Excel® are trademarks or registered trademarks of Microsoft
Corporation, © Microsoft Corporation, in the United States and other countries. Use of
this material does not imply Microsoft sponsorship, affiliation, or endorsement. IBM® and
SPSS® are trademarks or registered trademarks of International Business Machines
Corporation, registered in many jurisdictions worldwide. StatPlus LE® and StatPlus Pro®
are trademarks of AnalystSoft, Inc.
Notice of Liability
The information in this book is distributed on an “as is” basis, without warranty.
While every precaution has been taken in the preparation of this book, the publisher and
author make no claim or guarantee as to its correctness, usefulness, or completeness for
any reason, under any circumstance. Moreover, the publisher and author shall have no
liability to any person or entity with respect to loss or damages caused or alleged to have
been caused directly or indirectly by the information contained in this book.
Links to Web Sites
This book contains links to websites operated by third parties that are not under our
control and are provided to you for your convenience only. We make no warranties or
representations whatsoever about any such sites that you may access through this book or
any services, information, or products that they may provide.
Publisher’s Cataloging-in-Publication Data
Rovai, Alfred P.
Statistical Fundamentals: Using Microsoft Excel for Univariate and Bivariate
Analysis/ Alfred P. Rovai — Third Edition p. cm.
Includes bibliographical references, glossary, and index.
Contents: Introductory statistical analysis using Microsoft Excel software —
descriptive and inferential statistics, evaluation of test assumptions, and interpretation
and reporting of statistical results.
ISBN: 978-0-9911046-4-2 (pbk.)
1. Statistical methods—Computer programs. HA32.R68 2014 005.54—dc22

Library of Congress Control Number: 2015953468
Printed in the United States of America
Table of Contents
Chapter 1: Quantitative Research 1
1.1: Foundational Concepts 1
Introduction 1
Constructs 2
Sampling 3
Measurement 7
1.2: Data Ethics 18
Principles 18
Statistical Reporting 20
1.3: Microsoft Excel Fundamentals 23
Cell Formatting 26
Cell Addressing 30
Entering Data 30
Entering Independent and Dependent Data 31
Entering Formulas 33
Tables 36
Pivot Tables 38
Generating Random Numbers 50
1.4: Summary of Key Concepts 51
1.5: Chapter 1 Review 52
Chapter 2: Descriptive Statistics 57
2.1: Introduction to Descriptive Statistics 57
Sample Size 60
2.2: Measures of Central Tendency 61
Mean 63
Standard Error of the Mean 64
Median 65
Mode 66
2.3: Measures of Dispersion 70
Variance 72
Standard Deviation 73
Maximum & Minimum 75
Range 76
Interquartile Range 77
Percent Distribution 78
2.4: Measures of Shape 80
Coefficient of Skewness 80
Standard Error of Skewness 82
Standard Coefficient of Skewness 83
Coefficient of Kurtosis 83
Standard Error of Kurtosis 85
Standard Coefficient of Kurtosis 85
2.5: Measures of Relative Position 87
Percentile 88
Quartile 90
2.6: Normal Curve 92
The Normal Distribution 92
Transforming Raw Scores Into Standard Scores 97
Z-Score, N(0,1) 97
T-Score, N(50,10) 100
Normal Curve Equivalent (NCE) Score, N(50, 21.06) 101
Stanine Scores 103
Standardized Norm-Referenced Testing 105
2.7: Charts 106
Creating Charts 106
Line Chart 109
Area Chart 117
Column Chart 121
Bar Chart 125
Scatterplot 129
Histogram 134
Pie Chart 148
2.8: Analysis ToolPak and StatPlus Procedures 152
2.9: Summary of Key Concepts 156
2.10: Chapter 2 Review 159
Chapter 3: Inferential Statistics 163
3.1: Basic Concepts 163
Introduction 163
Probability 167
Parameter Estimation 169
Hypothesis Testing 181
Controlling Type I Error 196
3.2: Evaluating Test Assumptions 198
Introduction 198
Independence of Observations 198
Measurement Without Error 199
Normality 199
Absence of Extreme Outliers 203
Linearity 204
Homogeneity of Variance 206
Homoscedasticity 207
Sphericity 207
Absence of Restricted Range 208
Dealing with Deviations 208
3.3: Summary of Key Concepts 210
3.4: Chapter 3 Review 211
Chapter 4: Hypothesis Tests 217
4.1: Hypothesis Test Overview 217
4.2: Goodness-of-Fit Tests 222
One-Sample t-Test 223
Chi-Square Goodness-of-Fit Test 230
Kolmogorov-Smirnov Test 236
4.3: Comparing Two Independent Samples 245
F-Test of Equality of Variance 246
Levene’s Test 253
Independent t-Test 259
MannWhitney U Test 272
4.4: Comparing Multiple Independent Samples 279
One-Way Between Subjects ANOVA 280
Kruskal-Wallis H Test 293
Post Hoc Multiple Comparison Tests 299
4.5: Comparing Two Dependent Samples 301
Dependent t-Test 302
Wilcoxon Matched-Pair Signed Ranks Test 312
Related Samples Sign Test 319
McNemar Test 324
4.6: Comparing Multiple Dependent Samples 331
One-Way Within Subjects ANOVA 332
Post Hoc Trend Analysis 345
Friedman Test 347
4.7: Association 356
Introduction 357