Data Analysis with Microsoft Excel™ Updated for Office 2007 (Book Only)

by Berk, Kenneth N.; Carey, Patrick M.

• ISBN13: 9780495391784
• ISBN10: 0495391786
• Edition: 3rd
• Format: Paperback
• Copyright: 2009-06-18
• Publisher: Cengage Learning

Summary

This popular best-selling book shows students and professionals how to do data analysis with Microsoft? Excel. DATA ANALYSIS WITH MICROSOFT EXCEL teaches the fundamental concepts of statistics and how to use Microsoft Excel to solve the kind of data-intensive problems that arise in business and elsewhere. Even students with no previous experience using spreadsheets will find that this text?s step-by-step approach, extensive tutorials, and examples make it easy to learn how to use Excel for analyzing data. A downloadable StatPlus? add-in for Microsoft Excel, data sets for exercises, and interactive concept tutorials are available on the Book Companion Website.

Table of Contents

Getting Started with Excel	p. 1
Getting Started	p. 2
Special Files for This Book	p. 2
Installing the StatPlus Files	p. 2
Excel and Spreadsheets	p. 4
Launching Excel	p. 5
Viewing the Excel Window	p. 6
Running Excel Commands	p. 7
Excel Workbooks and Worksheets	p. 10
Opening a Workbook	p. 10
Scrolling through a Workbook	p. 11
Worksheet Cells	p. 14
Selecting a Cell	p. 14
Moving Cells	p. 16
Printing from Excel	p. 18
Previewing the Print Job	p. 18
Setting Up the Page	p. 19
Printing the Page	p. 21
Saving Your Work	p. 22
Excel Add-Ins	p. 24
Loading the StatPlus Add-In	p. 24
Loading the Data Analysis ToolPak	p. 28
Unloading an Add-In	p. 30
Features of StatPlus	p. 30
Using StatPlus Modules	p. 30
Hidden Data	p. 31
Linked Formulas	p. 32
Setup Options	p. 32
Exiting Excel	p. 34
Working with Data	p. 35
Data Entry	p. 36
Entering Data from the Keyboard	p. 36
Entering Data with Autofill	p. 37
Inserting New Data	p. 40
Data Formats	p. 41
Formulas and Functions	p. 45
Inserting a Simple Formula	p. 46
Inserting an Excel Function	p. 47
Cell References	p. 50
Range Names	p. 51
Sorting Data	p. 54
Querying Data	p. 55
Using the AutoFilter	p. 56
Using the Advanced Filter	p. 59
Using Calculated Values	p. 62
Importing Data from Text Files	p. 63
Importing Data from Databases	p. 68
Using Excel's Database Query Wizard	p. 68
Specifying Criteria and Sorting Data	p. 71
Exercises	p. 75
Working with Charts	p. 81
Introducing Excel Charts	p. 82
Introducing Scatter Plots	p. 86
Editing a Chart	p. 91
Resizing and Moving an Embedded Chart	p. 91
Moving a Chart to a Chart Sheet	p. 93
Working with Chart and Axis Titles	p. 94
Editing the Chart Axes	p. 97
Working with Gridlines and Legends	p. 100
Editing Plot Symbols	p. 102
Identifying Data Points	p. 105
Selecting a Data Row	p. 106
Labeling Data Points	p. 107
Formatting Labels	p. 109
Creating Bubble Plots	p. 110
Breaking a Scatter Plot into Categories	p. 117
Plotting Several Variables	p. 120
Exercises	p. 123
Describing Your Data	p. 128
Variables and Descriptive Statistics	p. 129
Frequency Tables	p. 131
Creating a Frequency Table	p. 132
Using Bins in a Frequency Table	p. 134
Defining Your Own Bin Values	p. 136
Working with Histograms	p. 138
Creating a Histogram	p. 138
Shapes of Distributions	p. 141
Breaking a Histogram into Categories	p. 143
Working with Stem and Leaf Plots	p. 146
Distribution Statistics	p. 151
Percentiles and Quartiles	p. 151
Measures of the Center: Means, Medians, and the Mode	p. 154
Measures of Variability	p. 159
Measures of Shape: Skewness and Kurtosis	p. 162
Outliers	p. 164
Working with Boxplots	p. 165
Concept Tutorials: Boxplots	p. 166
Exercises	p. 175
Probability Distributions	p. 182
Probability	p. 183
Probability Distributions	p. 184
Discrete Probability Distributions	p. 185
Continuous Probability Distributions	p. 186
Concept Tutorials: PDFs	p. 187
Random Variables and Random Samples	p. 189
Concept Tutorials: Random Samples	p. 190
The Normal Distribution	p. 193
Concept Tutorials: The Normal Distribution	p. 194
Excel Worksheet Functions	p. 196
Using Excel to Generate Random Normal Data	p. 197
Charting Random Normal Data	p. 199
The Normal Probability Plot	p. 201
Parameters and Estimators	p. 205
The Sampling Distribution	p. 206
Concept Tutorials: Sampling Distributions	p. 211
The Standard Error	p. 212
The Central Limit Theorem	p. 212
Concept Tutorials: The Central Limit Theorem	p. 213
Exercises	p. 218
Statistical Inference	p. 224
Confidence Intervals	p. 225
z Test Statistic and z Values	p. 225
Calculating the Confidence Interval with Excel	p. 228
Interpreting the Confidence Interval	p. 229
Concept Tutorials: The Confidence Interval	p. 229
Hypothesis Testing	p. 232
Types of Error	p. 233
An Example of Hypothesis Testing	p. 234
Acceptance and Rejection Regions	p. 234
p Values	p. 235
Concept Tutorials: Hypothesis Testing	p. 236
Additional Thoughts about Hypothesis Testing	p. 239
The t Distribution	p. 240
Concept Tutorials: The t Distribution	p. 241
Working with the t Statistic	p. 242
Constructing a t Confidence Interval	p. 243
The Robustness of t	p. 243
Applying the t Test to Paired Data	p. 244
Applying a Nonparametric Test to Paired Data	p. 250
The Wilcoxon Signed Rank Test	p. 250
The Sign Test	p. 253
The Two-Sample t Test	p. 255
Comparing the Pooled and Unpooled Test Statistics	p. 256
Working with the Two-Sample t Statistic	p. 256
Testing for Equality of Variance	p. 258
Applying the t Test to Two-Sample Data	p. 259
Applying a Nonparametric Test to Two-Sample Data	p. 265
Final Thoughts about Statistical Inference	p. 267
Exercises	p. 268
Tables	p. 275
PivotTables	p. 276
Removing Categories from a PivotTable	p. 280
Changing the Values Displayed by the PivotTable	p. 282
Displaying Categorical Data in a Bar Chart	p. 283
Displaying Categorical Data in a Pie Chart	p. 285
Two-Way Tables	p. 288
Computing Expected Counts	p. 291
The Pearson Chi-Square Statistic	p. 293
Concept Tutorials: The ¿2 Distribution	p. 293
Working with the ¿2 Distribution in Excel	p. 296
Breaking Down the Chi-Square Statistic	p. 297
Other Table Statistics	p. 297
Validity of the Chi-Square Test with Small Frequencies	p. 299
Tables with Ordinal Variables	p. 302
Testing for a Relationship between Two Ordinal Variables	p. 303
Custom Sort Order	p. 307
Exercises	p. 309
Regression and Correlation	p. 313
Simple Linear Regression	p. 314
The Regression Equation	p. 314
Fitting the Regression Line	p. 315
Regression Functions in Excel	p. 316
Exploring Regression	p. 317
Performing a Regression Analysis	p. 318
Plotting Regression Data	p. 320
Calculating Regression Statistics	p. 323
Interpreting Regression Statistics	p. 325
Interpreting the Analysis of Variance Table	p. 326
Parameter Estimates and Statistics	p. 327
Residuals and Predicted Values	p. 328
Checking the Regression Model	p. 329
Testing the Straight-Line Assumption	p. 329
Testing for Normal Distribution of the Residuals	p. 331
Testing for Constant Variance in the Residuals	p. 332
Testing for the Independence of Residuals	p. 332
Correlation	p. 335
Correlation and Slope	p. 336
Correlation and Causality	p. 336
Spearman's Rank Correlation Coefficient s	p. 337
Correlation Functions in Excel	p. 337
Creating a Correlation Matrix	p. 338
Correlation with a Two-Valued Variable	p. 342
Adjusting Multiple p Values with Bonferroni	p. 342
Creating a Scatter Plot Matrix	p. 343
Exercises	p. 345
Multiple Regression	p. 352
Regression Models with Multiple Parameters	p. 353
Concept Tutorials: The F Distribution	p. 353
Using Regression for Prediction	p. 355
Regression Example: Predicting Grades	p. 356
Interpreting the Regression Output	p. 358
Multiple Correlation	p. 359
Coefficients and the Prediction Equation	p. 361
t Tests for the Coefficients	p. 362
Testing Regression Assumptions	p. 363
Observed versus Predicted Values	p. 363
Plotting Residuals versus Predicted Values	p. 366
Plotting Residuals versus Predictor Variables	p. 368
Normal Errors and the Normal Plot	p. 370
Summary of Calc Analysis	p. 371
Regression Example: Sex Discrimination	p. 371
Regression on Male Faculty	p. 372
Using a SPLOM to See Relationships	p. 373
Correlation Matrix of Variables	p. 374
Multiple Regression	p. 376
Interpreting the Regression Output	p. 377
Residual Analysis of Discrimination Data	p. 377
Normal Plot of Residuals	p. 378
Are Female Faculty Underpaid?	p. 380
Drawing Conclusions	p. 385
Exercises	p. 386
Analysis of Variance	p. 392
One-Way Analysis of Variance	p. 393
Analysis of Variance Example: Comparing Hotel Prices	p. 393
Graphing the Data to Verify ANOVA Assumptions	p. 395
Computing the Analysis of Variance	p. 397
Interpreting the Analysis of Variance Table	p. 399
Comparing Means	p. 402
Using the Bonferroni Correction Factor	p. 403
When to Use Bonferroni	p. 404
Comparing Means with a Boxplot	p. 405
One-Way Analysis of Variance and Regression	p. 406
Indicator Variables	p. 406
Fitting the Effects Model	p. 408
Two-Way Analysis of Variance	p. 410
A Two-Factor Example	p. 410
Two-Way Analysis Example: Comparing Soft Drinks	p. 413
Graphing the Data to Verify Assumptions	p. 414
The Interaction Plot	p. 417
Using Excel to Perform a Two-Way Analysis of Variance	p. 419
Interpreting the Analysis of Variance Table	p. 422
Summary	p. 424
Exercises	p. 424
Time Series	p. 431
Time Series Concepts	p. 432
Time Series Example: The Rise in Global Temperatures	p. 432
Plotting the Global Temperature Time Series	p. 433
Analyzing the Change in Global Temperature	p. 436
Looking at Lagged Values	p. 438
The Autocorrelation Function	p. 440
Applying the ACF to Annual Mean Temperature	p. 441
Other ACF Patterns	p. 443
Applying the ACF to the Change in Temperature	p. 444
Moving Averages	p. 445
Simple Exponential Smoothing	p. 448
Forecasting with Exponential Smoothing	p. 450
Assessing the Accuracy of the Forecast	p. 450
Concept Tutorials: One-Parameter Exponential Smoothing	p. 451
Choosing a Value for w	p. 455
Two-Parameter Exponential Smoothing	p. 457
Calculating the Smoothed Values	p. 458
Concept Tutorials: Two-Parameter Exponential Smoothing	p. 459
Seasonality	p. 462
Multiplicative Seasonality	p. 462
Additive Seasonality	p. 464
Seasonal Example: Liquor Sales	p. 464
Examining Seasonality with a Boxplot	p. 467
Examining Seasonality with a Line Plot	p. 468
Applying the ACF to Seasonal Data	p. 470
Adjusting for Seasonality	p. 471
Three-Parameter Exponential Smoothing	p. 473
Forecasting Liquor Sales	p. 474
Optimizing the Exponential Smoothing Constant (optional)	p. 479
Exercises	p. 482
Quality Control	p. 487
Statistical Quality Control	p. 488
Controlled Variation	p. 489
Uncontrolled Variation	p. 489
Control Charts	p. 490
Control Charts and Hypothesis Testing	p. 492
Variable and Attribute Charts	p. 493
Using Subgroups	p. 493
The <$>\bar {x}<$> Chart	p. 493
Calculating Control Limits When ¿ Is Known	p. 494
<$>\bar {x}<$> Chart Example: Teaching Scores	p. 495
Calculating Control Limits When ¿ Is Unknown	p. 498
<$>\bar {x}<$> Chart Example: A Coating Process	p. 500
The Range Chart	p. 502
The C Chart	p. 504
C Chart Example: Factory Accidents	p. 504
The P Chart	p. 506
P Chart Example: Steel Rod Defects	p. 507
Control Charts for Individual Observations	p. 509
The Pareto Chart	p. 513
Exercises	p. 517
Appendix	p. 521
Excel Reference	p. 581
Bibliography	p. 587
Index	p. 589
Table of Contents provided by Ingram. All Rights Reserved.