Statistical analysis: Microsoft Excel 2013
Saved in:
| Main Author: | |
|---|---|
| Format: | Book |
| Language: | English |
| Published: |
Indianapolis
Que
2014
|
| Series: | Always learning
|
| Subjects: | |
| Online Access: | Inhaltsverzeichnis |
| Physical Description: | XVII, 490 S. |
| ISBN: | 9780789753113 |
Staff View
MARC
| LEADER | 00000nam a2200000 c 4500 | ||
|---|---|---|---|
| 001 | BV042046223 | ||
| 003 | DE-604 | ||
| 005 | 20141013 | ||
| 007 | t | ||
| 008 | 140827s2014 |||| 00||| eng d | ||
| 020 | |a 9780789753113 |c print |9 978-0-7897-5311-3 | ||
| 035 | |a (OCoLC)891989864 | ||
| 035 | |a (DE-599)BVBBV042046223 | ||
| 040 | |a DE-604 |b ger |e rakwb | ||
| 041 | 0 | |a eng | |
| 049 | |a DE-355 | ||
| 084 | |a ST 371 |0 (DE-625)143672: |2 rvk | ||
| 100 | 1 | |a Carlberg, Conrad George |d 1948- |e Verfasser |0 (DE-588)129577006 |4 aut | |
| 245 | 1 | 0 | |a Statistical analysis |b Microsoft Excel 2013 |c Conrad Carlberg |
| 264 | 1 | |a Indianapolis |b Que |c 2014 | |
| 300 | |a XVII, 490 S. | ||
| 336 | |b txt |2 rdacontent | ||
| 337 | |b n |2 rdamedia | ||
| 338 | |b nc |2 rdacarrier | ||
| 490 | 0 | |a Always learning | |
| 650 | 0 | 7 | |a Statistische Analyse |0 (DE-588)4116599-8 |2 gnd |9 rswk-swf |
| 650 | 0 | 7 | |a Excel 2013 |0 (DE-588)1031919325 |2 gnd |9 rswk-swf |
| 689 | 0 | 0 | |a Statistische Analyse |0 (DE-588)4116599-8 |D s |
| 689 | 0 | 1 | |a Excel 2013 |0 (DE-588)1031919325 |D s |
| 689 | 0 | |C b |5 DE-604 | |
| 776 | 0 | 8 | |i Erscheint auch als |n Online-Ausgabe |z 978-0-13-382400-1 |
| 856 | 4 | 2 | |m Digitalisierung UB Regensburg - ADAM Catalogue Enrichment |q application/pdf |u http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=027487399&sequence=000002&line_number=0001&func_code=DB_RECORDS&service_type=MEDIA |3 Inhaltsverzeichnis |
| 999 | |a oai:aleph.bib-bvb.de:BVB01-027487399 | ||
Record in the Search Index
| _version_ | 1804152475737915392 |
|---|---|
| adam_text | Table
of
Contents
Introduction
.............................................................................. xi
Using Excel for Statistical Analysis
...........................................................................xi
About You and About Excel
..............................................................................xii
Clearing Up the Terms
..................................................................................xii
Making Things Easier
..................................................................................xiii
The Wrong Box?
......................................................................................xiv
Wagging the Dog
.....................................................................................xvi
What s in This Book
.......................................................................................xvi
1
About Variables and Values
..............................................................1
Variables and Values
........................................................................................1
Recording Data in Lists
...................................................................................2
Scales of Measurement
......................................................................................4
Category Scales
.........................................................................................5
Numeric Scales
.........................................................................................7
Telling an Interval Value from a Text Value
.................................................................8
Charting Numeric Variables in Excel
..........................................................................10
Charting Two Variables
.................................................................................10
Understanding Frequency Distributions
.......................................................................12
Using Frequency Distributions
...........................................................................15
Building a Frequency Distribution from a Sample
...........................................................18
Building Simulated Frequency Distributions
...............................................................26
2
How Values Cluster Together
...........................................................29
Calculating the Mean
......................................................................................30
Understanding Functions, Arguments, and Results
..........................................................31
Understanding Formulas, Results, and Formats
.............................................................34
Minimizing the Spread
..................................................................................36
Calculating the Median
.....................................................................................41
Choosing to Use the Median
.............................................................................41
Calculating the Mode
......................................................................................42
Getting the Mode ofCategories with a Formula
............................................................47
From
Centrai
Tendency to Variability
.........................................................................54
3
Variability: How Values Disperse
........................................................55
Measuring Variability with the Range
........................................................................56
The Concept of
з
Standard Deviation
.........................................................................58
Arranging for a Standard
................................................................................59
Thinking in Terms of Standard Deviations
.................................................................60
Calculating the Standard Deviation and Variance
...............................................................62
Squaring the Deviations.
................................................................................65
Population Parameters and Sample Statistics
..............................................................66
Dividing by
N — 1......................................................................................66
iv
Statistical Analysis: Microsoft Excel
2013
Bias in the Estimate.....
...................................................................................68
Degrees of Freedom
....................................................................................69
Exceľs
Variability Functions......
...........................................................................70
Standard Deviation Functions
............................................................................70
Variance Functions
.....................................................................................71
4
How Variables Move Jointly: Correlation
.................................................73
understanding Correlation
..................................................................................73
The Correlation, Calculated
..............................................................................75
Using the
CORRELO
Function
......................·.....................................................
81
Using the Analysis Tools
................................................................................84
Using the Correlation Tool
...............................................................................86
Correlation Isn t Causation
..............................................................................88
Using Correlation
..........................................................................................90
Removing the Effects of the Scale
........................................................................91
Using the Excel Function
................................................................................93
Getting the Predicted Values
............................................................................95
Getting the Regression Formula
..........................................................................96
Using
TRENĐ0
for Multiple Regression
.......................................................................99
Combining the Predictors
...............................................................................99
Understanding Best Combination
.....................................................................100
Understanding Shared Variance
........................................................................104
A Technical Note: Matrix Algebra and Multiple Regression in Excel
..........................................
1°6
Moving on to Statistical Inference
.........................................................................107
5
How Variables Classify Jointly: Contingency Tables
.......................................
W9
Understanding One-Way Pivot Tables
......................................................................109
Running the Statistical Test
...........................................................................112
Making Assumptions
.....................................................................................117
Random Selection
....................................................................................118
Independent Selections
...............................................................................119
The Binomial Distribution Formula
.....................................................................120
Using the BINOM.INVQ Function
.......................................................................121
Understanding Two-Way Pivot Tables
......................................................................127
Probabilities and Independent Events
...................................................................130
Testing the independence of Classifications
..............................................................131
The Yule Simpson effect
..................................................................................137
Summarizing the Chi-Square Functions
.....................................................................140
Using CHISQ.DISTO
................................................ ...................................140
Using CHISQ.DIST.RTO and CHIDISTQ
....................................................................141
Using CHISQ.INVQ
....................................................................................143
Using CHiSQ.INV.RTO and CHIINVQ
.....................................................................143
Using CHISQ.TESTO and CHITESTQ
......................................................................144
Using Mixed and Absolute References to Calculate Expected Frequencies
....................................145
Using the Pivot Table s Index Display
...................................................................146
Contents
6
Telling
the Truth with Statistics
........................................................149
A Context for Inferential Statistics
.........................................................................150
Establishing Internal Validity
..........................................................................151
Threats to internal Validity
............................................................................152
Problems with Excel s Documentation
......................................................................156
The F-Test Two-Sample for Variances
......................................................................157
Why Run the Test?
...................................................................................158
A Final Point
........................................................................................169
7
Using Excel with the Normal Distribution
................................................171
About the Normal Distribution
............................................................................ 171
Characteristics of the Normal Distribution
............................................................... 171
The Unit Normal Distribution
.......................................................................... 176
Excel Functions for the Normal Distribution
................................................................. 177
The NORM-DISTO Function
............................................................................177
The
N0RM.INV0
Function
............................................................................. 180
Confidence Intervals and the Normal Distribution
............................................................ 182
The Meaning of a Confidence Interval
................................................................... 183
Constructing a Confidence Interval
..................................................................... 184
Excel Worksheet Functions That Calculate Confidence Intervals
............................................. 187
Using CONFIDEMCLNORMO and CONFIDENCEO
........................................................... 188
Using CONFIDENCE.^)
................................................................................ 191
Using the Data Analysis Add-In for Confidence Intervals
................................................... 192
Confidence Intervals and Hypothesis Testing
............................................................. 194
The Central Limit Theorem
................................................................................194
Making Things Easier
.................................................................................196
Making Things Better
.................................................................................198
8
Testing Differences Between Means: The Basics
..........................................199
Testing Means: The Rationale
.............................................................................200
Using a z-Test
.......................................................................................201
Using the Standard Error of the Mean
...................................................................204
Creating the Charts
...................................................................................208
Using the t-Test Instead of the z-Test
.......................................................................216
Defining the Decision Rule
............................................................................218
Understanding Statistical Power
....................................................................... 222
9
Testing Differences Between Means: Further issues
......................................227
Using Excel s T.DISTO and T.INV0 Functions to Test Hypotheses
................................................227
Making Directional and Nondirectional Hypotheses
....................................................... 228
Using Hypotheses to Guide Excel s t-Distribution Functions
................................................229
Completing the Picture with T.DISTO
...................................................................237
Using the T.TESTO Function
...............................................................................238
Degrees of Freedom in Excel Functions
.......................................,.,........................238
Equal and Unequal Group Sizes
........................................................................239
The T.TESTO Syntax
............................·.....................................................242
vi
Statistical Analysis: Microsoft Excel
2013
Using the Data Analysis Add-in t~Tests
.....................................................................255
Group Variances in t-Tests.
............................................................................255
Visualizing Statistical Power,
..........................................................................260
When to Avoid t-Tests
................................................................................261
10
Testing Differences Between Means: The Analysis of Variance
..............................263
Why Not t-Tests?
........................................................................................263
The Logic of ANOVA
......................................................................................265
Partitioning the Scores
................................................................................265
Comparing Variances..
...............................................................................268
TheFTest
...........................................................................................273
Using Excel s Worksheet Functions for the
F
Distribution
......................................................277
Using F.DISTO and F.DIST.RTO
.........................................................................277
Using F.INV0 and RNVO
..............................................................................278
The
F
Distribution
....................................................................................279
Unequal Group Sizes
.....................................................................................280
Multiple Comparison Procedures
..........................................................................282
The
Scheffé
Procedure
................................................................................284
Planned Orthogonal Contrasts
.........................................................................289
11
Analysis of Variance: Further Issues
.....................................................293
Factorial ANOVA
.........................................................................................293
Other
Rationales
for Multiple Factors
...................................................................294
Using the Two-Factor ANOVA Tool
......................................................................297
The Meaning of
Interađion
...............................................................................299
The Statistical Significance of an Interaction
.............................................................
300
Calculating the Interaaion Effect
.......................................................................302
The Problem of Unequal Group Sizes
.......................................................................307
Repeated Measures: The Two Factor Without Replication Tool
..............................................
309
Excel s Functions and Tools: Limitations and Solutions
........................................................
31°
Mixed Models
.......................................................................................312
Power of the
F
Test
...................................................................................312
12
Experimental Design and ANOVA
.......................................................315
Crossed Factors and Nested Factors
........................................................................315
Depicting the Design Accurately
........................................................................317
Nuisance Factors
.....................................................................................317
Fixed Factors and Random Factors
............................................,............................318
The Data Analysis Add-ln s ANOVA Tools
................................................................319
Data Layout
.........................................................................................320
Calculating the
F
Ratios
..................................................................................322
Adapting the Data Analysis Tool for a Random Factor
.....................................................322
Designing the
F
Test
..................................................................................323
The Mixed Model: Choosing the Denominator
............................................................325
Adapting the Data Analysis Tool for a Nested Factor
.......................................................326
Contents
vii
Data Layout for a Nested Design
.......................................................................327
Getting the Sums of Squares
...........................................................................328
Calculating the
F
Ratio for the Nesting Factor
............................................................329
13
Statistical Power
.....................................................................331
Controlling the Risk
......................................................................................331
Directional and Nondirectional Hypotheses
..............................................................332
Changing the
Sampie
Size
.............................................................................332
Visualizing Statistical Power
...........................................................................333
Quantifying Power
...................................................................................335
The Statistical Power of t-Tests
............................................................................337
Nondirectional Hypotheses
............................................................................338
Making a Directional Hypothesis
.......................................................................340
Increasing the Size of the Samples
......................................................................341
The Dependent Groups t-Test
..........................................................................342
The Noncentrality Parameter in the
F
Distribution
............................................................344
Variance Estimates
...................................................................................344
The Noncentrality Parameter and the Probability Density Function
..........................................348
Calculating the Power of the
F
Test
........................................................................350
Calculating the Cumulative Density Function
.............................................................350
Using Power to Determine Sample Size
.................................................................352
14
Multiple Regression Analysis and Effect Coding: The Basics
................................355
Multiple Regression and ANOVA
...........................................................................356
Using Effect Coding
..................................................................................358
Effect Coding: General Principles
.......................................................................358
Other Types of Coding
................................................................................359
Multiple Regression and Proportions of Variance
.............................................................360
Understanding the Segue from ANOVA to Regression
.....................................................363
The Meaning of Effect Coding
..........................................................................365
Assigning Effect Codes in Excel
............................................................................368
Using Excel s Regression Tool with Unequal Group Sizes
.......................................................370
Effect Coding, Regression, and Factorial Designs in Excel
......................................................372
Exerting Statistical ControJ with Semipartial Correlations
..................................................374
Using a Squared Semipartial to Get the
Corred
Sum of Squares
.............................................376
Using TrendO to Replace Squared Semipartial Correlations
....................................................377
Working With the Residuals
...........................................................................379
Using Excel s Absolute and Relative Addressing to Extend the Semipartials
....................„............,. 381
15
Multiple Regression Analysis and Effect Coding: Further Issues
.............................385
Solving Unbalanced Factorial Designs Using Multiple Regression
............................................... 385
Variables Are Uncorrelated in a Balanced Design
.........................................................386
Variables Are Correlated in an Unbalanced Design
........................................................388
Order of Entry Is Irrelevant in the Balanced Design
........................................................388
Order Entry Is Important in the Unbalanced Design
.......................................................391
About Fluctuating Proportions of Variance
...............................................................393
viii
Statistical Analysis: Microsoft Excel
2013
Experimental Designs, Observational Studies, and Correlation
.................................................394
Using All the LINESTO Statistics
...................................... ......................................397
Using the Regression Coefficients
......................................................................398
Using the Standard Errors
.............................................................................398
Dealing with the Intercept
............................................................................399
Understanding UNESTO s Third, Fourth, and Fifth Rows
...................................................400
Getting the Regression Coefficients
.....................................................................406
Getting the Sum of Squares Regression and Residual
......................................................410
Calculating the Regression Diagnostics
..................................................................412
How LINESTO Handles Muiticollinearity
.................................................................416
Forcing a Zero Constant
...............................................................................421
The Excel
2007
Version
...............................................................................422
A Negative R2?
......................................................................................425
Managing Unequal Group Sizes in a True Experiment
.........................................................428
Managing Unequal Group Sizes in Observational Research
....................................................430
16
Analysis of Covariance: The Basics
......................................................433
The Purposes of ANCOVA
.................................................................................434
Greater Power
.......................................................................................434
Bias Reduction
.......................................................................................434
Using ANCOVA to increase Statistical Power
.................................................................435
ANOVA Finds No Significant Mean Difference
............................................................436
Adding a Covariate to the Analysis
......................................................................437
Testing for a Common Regression Line
.....................................................................445
Removing Bias: A Different Outcome
.......................................................................447
17
Analysis of Covariance: Further Issues
...................................................453
Adjusting Means with LINESTO
and Effect Coding
....................................................................................453
Effect Coding and Adjusted Group Means
...................................................................458
Multiple Comparisons Following ANCOVA
...................................................................461
Using the
Scheffé
Method
.............................................................................462
Using Planned Contrasts
..............................................................................466
The Analysis of Multiple Covariance
........................................................................468
The Decision to Use Multiple Covariates
.................................................................469
Two Covariates: An Example
...........................................................................470
Index
...............................................................................473
|
| any_adam_object | 1 |
| author | Carlberg, Conrad George 1948- |
| author_GND | (DE-588)129577006 |
| author_facet | Carlberg, Conrad George 1948- |
| author_role | aut |
| author_sort | Carlberg, Conrad George 1948- |
| author_variant | c g c cg cgc |
| building | Verbundindex |
| bvnumber | BV042046223 |
| classification_rvk | ST 371 |
| ctrlnum | (OCoLC)891989864 (DE-599)BVBBV042046223 |
| discipline | Informatik |
| format | Book |
| fullrecord | <?xml version="1.0" encoding="UTF-8"?><collection xmlns="http://www.loc.gov/MARC21/slim"><record><leader>01441nam a2200361 c 4500</leader><controlfield tag="001">BV042046223</controlfield><controlfield tag="003">DE-604</controlfield><controlfield tag="005">20141013 </controlfield><controlfield tag="007">t</controlfield><controlfield tag="008">140827s2014 |||| 00||| eng d</controlfield><datafield tag="020" ind1=" " ind2=" "><subfield code="a">9780789753113</subfield><subfield code="c">print</subfield><subfield code="9">978-0-7897-5311-3</subfield></datafield><datafield tag="035" ind1=" " ind2=" "><subfield code="a">(OCoLC)891989864</subfield></datafield><datafield tag="035" ind1=" " ind2=" "><subfield code="a">(DE-599)BVBBV042046223</subfield></datafield><datafield tag="040" ind1=" " ind2=" "><subfield code="a">DE-604</subfield><subfield code="b">ger</subfield><subfield code="e">rakwb</subfield></datafield><datafield tag="041" ind1="0" ind2=" "><subfield code="a">eng</subfield></datafield><datafield tag="049" ind1=" " ind2=" "><subfield code="a">DE-355</subfield></datafield><datafield tag="084" ind1=" " ind2=" "><subfield code="a">ST 371</subfield><subfield code="0">(DE-625)143672:</subfield><subfield code="2">rvk</subfield></datafield><datafield tag="100" ind1="1" ind2=" "><subfield code="a">Carlberg, Conrad George</subfield><subfield code="d">1948-</subfield><subfield code="e">Verfasser</subfield><subfield code="0">(DE-588)129577006</subfield><subfield code="4">aut</subfield></datafield><datafield tag="245" ind1="1" ind2="0"><subfield code="a">Statistical analysis</subfield><subfield code="b">Microsoft Excel 2013</subfield><subfield code="c">Conrad Carlberg</subfield></datafield><datafield tag="264" ind1=" " ind2="1"><subfield code="a">Indianapolis</subfield><subfield code="b">Que</subfield><subfield code="c">2014</subfield></datafield><datafield tag="300" ind1=" " ind2=" "><subfield code="a">XVII, 490 S.</subfield></datafield><datafield tag="336" ind1=" " ind2=" "><subfield code="b">txt</subfield><subfield code="2">rdacontent</subfield></datafield><datafield tag="337" ind1=" " ind2=" "><subfield code="b">n</subfield><subfield code="2">rdamedia</subfield></datafield><datafield tag="338" ind1=" " ind2=" "><subfield code="b">nc</subfield><subfield code="2">rdacarrier</subfield></datafield><datafield tag="490" ind1="0" ind2=" "><subfield code="a">Always learning</subfield></datafield><datafield tag="650" ind1="0" ind2="7"><subfield code="a">Statistische Analyse</subfield><subfield code="0">(DE-588)4116599-8</subfield><subfield code="2">gnd</subfield><subfield code="9">rswk-swf</subfield></datafield><datafield tag="650" ind1="0" ind2="7"><subfield code="a">Excel 2013</subfield><subfield code="0">(DE-588)1031919325</subfield><subfield code="2">gnd</subfield><subfield code="9">rswk-swf</subfield></datafield><datafield tag="689" ind1="0" ind2="0"><subfield code="a">Statistische Analyse</subfield><subfield code="0">(DE-588)4116599-8</subfield><subfield code="D">s</subfield></datafield><datafield tag="689" ind1="0" ind2="1"><subfield code="a">Excel 2013</subfield><subfield code="0">(DE-588)1031919325</subfield><subfield code="D">s</subfield></datafield><datafield tag="689" ind1="0" ind2=" "><subfield code="C">b</subfield><subfield code="5">DE-604</subfield></datafield><datafield tag="776" ind1="0" ind2="8"><subfield code="i">Erscheint auch als</subfield><subfield code="n">Online-Ausgabe</subfield><subfield code="z">978-0-13-382400-1</subfield></datafield><datafield tag="856" ind1="4" ind2="2"><subfield code="m">Digitalisierung UB Regensburg - ADAM Catalogue Enrichment</subfield><subfield code="q">application/pdf</subfield><subfield code="u">http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=027487399&sequence=000002&line_number=0001&func_code=DB_RECORDS&service_type=MEDIA</subfield><subfield code="3">Inhaltsverzeichnis</subfield></datafield><datafield tag="999" ind1=" " ind2=" "><subfield code="a">oai:aleph.bib-bvb.de:BVB01-027487399</subfield></datafield></record></collection> |
| id | DE-604.BV042046223 |
| illustrated | Not Illustrated |
| indexdate | 2024-07-10T01:11:18Z |
| institution | BVB |
| isbn | 9780789753113 |
| language | English |
| oai_aleph_id | oai:aleph.bib-bvb.de:BVB01-027487399 |
| oclc_num | 891989864 |
| open_access_boolean | |
| owner | DE-355 DE-BY-UBR |
| owner_facet | DE-355 DE-BY-UBR |
| physical | XVII, 490 S. |
| publishDate | 2014 |
| publishDateSearch | 2014 |
| publishDateSort | 2014 |
| publisher | Que |
| record_format | marc |
| series2 | Always learning |
| spelling | Carlberg, Conrad George 1948- Verfasser (DE-588)129577006 aut Statistical analysis Microsoft Excel 2013 Conrad Carlberg Indianapolis Que 2014 XVII, 490 S. txt rdacontent n rdamedia nc rdacarrier Always learning Statistische Analyse (DE-588)4116599-8 gnd rswk-swf Excel 2013 (DE-588)1031919325 gnd rswk-swf Statistische Analyse (DE-588)4116599-8 s Excel 2013 (DE-588)1031919325 s b DE-604 Erscheint auch als Online-Ausgabe 978-0-13-382400-1 Digitalisierung UB Regensburg - ADAM Catalogue Enrichment application/pdf http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=027487399&sequence=000002&line_number=0001&func_code=DB_RECORDS&service_type=MEDIA Inhaltsverzeichnis |
| spellingShingle | Carlberg, Conrad George 1948- Statistical analysis Microsoft Excel 2013 Statistische Analyse (DE-588)4116599-8 gnd Excel 2013 (DE-588)1031919325 gnd |
| subject_GND | (DE-588)4116599-8 (DE-588)1031919325 |
| title | Statistical analysis Microsoft Excel 2013 |
| title_auth | Statistical analysis Microsoft Excel 2013 |
| title_exact_search | Statistical analysis Microsoft Excel 2013 |
| title_full | Statistical analysis Microsoft Excel 2013 Conrad Carlberg |
| title_fullStr | Statistical analysis Microsoft Excel 2013 Conrad Carlberg |
| title_full_unstemmed | Statistical analysis Microsoft Excel 2013 Conrad Carlberg |
| title_short | Statistical analysis |
| title_sort | statistical analysis microsoft excel 2013 |
| title_sub | Microsoft Excel 2013 |
| topic | Statistische Analyse (DE-588)4116599-8 gnd Excel 2013 (DE-588)1031919325 gnd |
| topic_facet | Statistische Analyse Excel 2013 |
| url | http://bvbr.bib-bvb.de:8991/F?func=service&doc_library=BVB01&local_base=BVB01&doc_number=027487399&sequence=000002&line_number=0001&func_code=DB_RECORDS&service_type=MEDIA |
| work_keys_str_mv | AT carlbergconradgeorge statisticalanalysismicrosoftexcel2013 |