Statistical Analysis - Carlberg_ Conrad.pdf
(
16002 KB
)
Pobierz
C o n t e n t s
a t
a
G l a n c e
Introduction
...............................................................................................................
1
1
About Variables and Values
................................................................................
9
2
How Values Cluster Together
. .........................................................................
35
3
Variability: How Values Disperse
. ...................................................................
61
4
How Variables Move Jointly: Correlation
. ...................................................
79
5
How Variables Classify Jointly: ContingencyTables
. ............................
113
6
Telling the Truth with Statistics
. .................................................................
149
7
Using Excel with the Normal Distribution
. ..............................................
169
8
Testing Differences Between Means: The Basics
. .................................
197
9
Testing Differences Between Means: Further Issues
. ..........................
225
10
Testing Differences Between Means: The Analysis of Variance
.......
259
11
Analysis of Variance: Further Issues
. ..........................................................
287
StatiStical analySiS
MicroSoft
®
ExcEl 2010
12
Multiple Regression Analysis and Effect Coding: The Basics
. ...........
307
13
Multiple Regression Analysis: Further Issues
. ........................................
337
14
Analysis of Covariance: The Basics
. .............................................................
361
15
AnalysisofCovariance:FurtherIssues
. .....................................................
381
Index. ............................................................................................399
Conrad Carlberg
800 East 96th Street,
Indianapolis, Indiana 46240 USA
Statistical Analysis: Microsoft® Excel 2010
Copyright © 2011 by Pearson Education, Inc.
Editor in Chief
Greg Wiegand
Acquisitions Editor
Loretta Yates
Development Editor
Abshier House
Managing Editor
Sandra Schroeder
Senior Project Editor
Tonya Simpson
Copy Editor
Bart Reed
Indexer
Tim Wright
Proofreader
Leslie Joseph
Technical Editor
Linda Sikorski
Publishing Coordinator
Cindy Teeters
Book Designer
Anne Jones
Compositor
Jake McFarland
All rights reserved. No part of this book shall be reproduced,
stored in a retrieval system, or transmitted by any means, elec-
tronic, mechanical, photocopying, recording, or otherwise, with-
out written permission from the publisher. No patent liability
is assumed with respect to the use of the information contained
herein. Although every precaution has been taken in the prepara-
tion of this book, the publisher and author assume no respon-
sibility for errors or omissions. Nor is any liability assumed for
damages resulting from the use of the information contained
herein.
Library of Congress Cataloging-in-Publication Data is on file.
ISBN-13: 978-0-7897-4720-4
ISBN-10: 0-7897-4720-0
Printed in the United States of America
First Printing: April 2011
Trademarks
All terms mentioned in this book that are known to be trade-
marks or service marks have been appropriately capitalized. Que
Publishing cannot attest to the accuracy of this information. Use of
a term in this book should not be regarded as affecting the validity
of any trademark or service mark.
Microsoft is a registered trademark of Microsoft Corporation.
Warning and Disclaimer
Every effort has been made to make this book as complete and
as accurate as possible, but no warranty or fitness is implied. The
information provided is on an “as is” basis. The author and the
publisher shall have neither liability nor responsibility to any per-
son or entity with respect to any loss or damages arising from the
information contained in this book.
Bulk Sales
Que Publishing offers excellent discounts on this book when
ordered in quantity for bulk purchases or special sales. For more
information, please contact
U.S. Corporate and Government Sales
1-800-382-3419
corpsales@pearsontechgroup.com
For sales outside the United States, please contact
International Sales
international@pearson.com
Table of Contents
Introduction
. ............................................................................................................................................................................................
1
Using
Excel
for
Statistical
Analysis
..............................................................................................................................................
1
About
You
and
About
Excel
. ..................................................................................................................................................
2
Clearing
Up
the
Terms
. .............................................................................................................................................................
3
Making
Things
Easier
. ...............................................................................................................................................................
3
The
Wrong
Box?
. ..........................................................................................................................................................................
4
Wagging
the
Dog
.........................................................................................................................................................................
6
What’s
in
This
Book
. ...........................................................................................................................................................................
6
1
About Variables and Values
. ..........................................................................................................................................
9
Variables
and
Values
...........................................................................................................................................................................
9
Recording
Data
in
Lists
..........................................................................................................................................................
10
Sca
les
of
Measurement
...................................................................................................................................................................
12
Category
Scales
...........................................................................................................................................................................
12
Numeric
Scales
............................................................................................................................................................................
14
Telling
an
Interval
Value
from
a
Text
Value
..............................................................................................................
15
Ch
arting
Numeric
Variables
in
Excel
.......................................................................................................................................
17
Charting
Two
Variables
..........................................................................................................................................................
17
Un
derstanding
Frequency
Distributions
..............................................................................................................................
19
Using
Frequency
Distributions
..........................................................................................................................................
22
Building
a
Frequency
Distribution
from
a
Sample
................................................................................................
25
Building
Simulated
Frequency
Distributions
............................................................................................................
31
2
Ho
w Values Cluster Together
. ..................................................................................................................................
35
Calculating
the
Mean
.......................................................................................................................................................................
36
Understanding
Functions,
Arguments,
and
Results
.............................................................................................
37
Understanding
Formulas,
Results,
and
Formats
....................................................................................................
40
Minimizing
the
Spread
...........................................................................................................................................................
41
Cal
culating
the
Median
..................................................................................................................................................................
46
ChoosingtoUsetheMedian
...............................................................................................................................................
47
Calculating
the
Mode
.......................................................................................................................................................................
48
Getting
the
Mode
of
Categories
with
a
Formula
....................................................................................................
53
From
Central
Tendency
to
Variability
....................................................................................................................................
59
3
Va
riability: How Values Disperse
..........................................................................................................................
61
Measuring
Variability
with
the
Range
..................................................................................................................................
62
The
Concept
of
a
Standard
Deviation
.....................................................................................................................................
64
Arranging
for
a
Standard
......................................................................................................................................................
65
Thinking
in
Terms
of
Standard
Deviations
.................................................................................................................
66
iv
Statistical Analysis: Microsoft Excel 2010
Calculating
the
Standard
Deviation
and
Variance
.........................................................................................................
68
Squaring
the
Deviations
........................................................................................................................................................
70
Population
Parameters
and
Sample
Statistics
.........................................................................................................
71
Dividing
by
N
−
1
......................................................................................................................................................................
72
Bias
in
the
Estimate
..........................................................................................................................................................................
74
Degrees
of
Freedom
.................................................................................................................................................................
74
Excel’s
Variability
Functions
........................................................................................................................................................
75
Standard
Deviation
Functions
...........................................................................................................................................
75
Variance
Functions
...................................................................................................................................................................
76
4
How Variables Move Jointly: Correlation
. ...................................................................................................
79
Understanding
Correlation
...........................................................................................................................................................
79
The
Correlation,
Calculated
.................................................................................................................................................
81
Using
the
CORREL()
Function
.............................................................................................................................................
86
Using
the
Analysis
Tools
........................................................................................................................................................
89
Using
the
Correlation
Tool
...................................................................................................................................................
91
Correlation
Isn’t
Causation
..................................................................................................................................................
93
Using
Correlation
................................................................................................................................................................................
95
Removing
the
Effects
of
the
Scale
...................................................................................................................................
96
Using
the
Excel
Function
.......................................................................................................................................................
98
Getting
the
Predicted
Values
..........................................................................................................................................
100
Getting
the
Regression
Formula
...................................................................................................................................
101
Using
TREND()
for
Multiple
Regression
.............................................................................................................................
104
Combining
the
Predictors
..................................................................................................................................................
104
Understanding
“Best
Combination”
...........................................................................................................................
105
Understanding
Shared
Variance
...................................................................................................................................
108
A
Technical
Note:
Matrix
Algebra
and
Multiple
Regression
in
Excel
.......................................................
110
Moving
on
to
Statistical
Inference
........................................................................................................................................
112
5
How Variables Classify Jointly: Contingency Tables
. ....................................................................
113
Understanding
One-Way
Pivot
Tables
...............................................................................................................................
113
Running
the
Statistical
Test
.............................................................................................................................................
116
Ma
king
Assumptions
....................................................................................................................................................................
120
Random
Selection
..................................................................................................................................................................
120
Independent
Selections
......................................................................................................................................................
122
The
Binomial
Distribution
Formula
.............................................................................................................................
122
Using
the
BINOM.INV()
Function
..................................................................................................................................
124
Un
derstanding
Two-Way
Pivot
Tables
...............................................................................................................................
129
Probabilities
and
Independent
Events
......................................................................................................................
132
Testing
the
Independence
of
Classifications
..........................................................................................................
133
Th
e
Yule
Simpson
Effect
..............................................................................................................................................................
139
Summarizing
the
Chi-Square
Functions
...................................................................................................................
141
Plik z chomika:
superktos86
Inne pliki z tego folderu:
Statistical Analysis - Carlberg_ Conrad.pdf
(16002 KB)
cover.jpg
(48 KB)
metadata.opf
(5 KB)
Inne foldery tego chomika:
Zgłoś jeśli
naruszono regulamin