363 pág.

# Excel Data Analysis For Dummies. 2nd Edition

DisciplinaExcel1.274 materiais1.987 seguidores
Pré-visualização18 páginas
233
Calculating Moving Averages ..................................................................... 235
Exponential Smoothing ............................................................................... 237
Generating Random Numbers .................................................................... 239
Sampling Data .............................................................................................. 241
Chapter 11: Inferential Statistics . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 245
Using the t-test Data Analysis Tool ........................................................... 246
Performing z-test Calculations ................................................................... 249
Creating a Scatter Plot ................................................................................ 251
Using the Regression Data Analysis Tool ................................................. 254
Using the Correlation Analysis Tool ......................................................... 257
Using the Covariance Analysis Tool .......................................................... 258
Using the ANOVA Data Analysis Tools ..................................................... 260
Creating an f-test Analysis .......................................................................... 261
Using Fourier Analysis ................................................................................ 262
Chapter 12: Optimization Modeling with Solver . . . . . . . . . . . . . . . . . 263
Understanding Optimization Modeling ..................................................... 263
Optimizing your imaginary profits .................................................. 264
Recognizing constraints .................................................................... 264
Setting Up a Solver Worksheet .................................................................. 265
Solving an Optimization Modeling Problem ............................................. 268
Reviewing the Solver Reports .................................................................... 273
The Answer Report ............................................................................ 273
The Sensitivity Report ....................................................................... 275
The Limits Report .............................................................................. 276
Some other notes about Solver reports .......................................... 277
Working with the Solver Options .............................................................. 277
Using the All Methods options ......................................................... 278
Using the GRG Nonlinear tab ............................................................ 279
Using the Evolutionary tab ............................................................... 281
Saving and reusing model information ........................................... 282
Understanding the Solver Error Messages ............................................... 282
Solver has found a solution .............................................................. 283
Solver has converged to the current solution ............................... 283
Solver cannot improve the current solution .................................. 283
Stop chosen when maximum time limit was reached ................... 283
Solver stopped at user\u2019s request ..................................................... 284
Stop chosen when maximum iteration limit was reached............ 284
Objective Cell values do not converge............................................ 284
Solver could not find a feasible solution ........................................ 284
Linearity conditions required by this LP Solver
are not satisfied .............................................................................. 285
The problem is too large for Solver to handle ............................... 285
Solver encountered an error value in a target
or constraint cell ............................................................................ 285
There is not enough memory available to
solve the problem .......................................................................... 286
Error in model. Please verify that all cells and
constraints are valid ...................................................................... 286
Excel Data Analysis For Dummies, 2nd Edition xii
Part IV: The Part of Tens ........................................... 287
Chapter 13: Ten Things You Ought to Know about Statistics . . . . . . 289
Descriptive Statistics Are Straightforward ............................................... 290
Averages Aren\u2019t So Simple Sometimes ..................................................... 290
Standard Deviations Describe Dispersion ................................................ 291
An Observation Is an Observation ............................................................ 292
A Sample Is a Subset of Values .................................................................. 293
Inferential Statistics Are Cool but Complicated ...................................... 293
Probability Distribution Functions Aren\u2019t Always Confusing ................ 294
Uniform distribution .......................................................................... 294
Normal distribution ........................................................................... 295
Parameters Aren\u2019t So Complicated ........................................................... 296
Skewness and Kurtosis Describe a Probability Distribution\u2019s Shape ... 297
Confidence Intervals Seem Complicated at First, but Are Useful .......... 297
Chapter 14: Almost Ten Tips for Presenting Table Results and
Analyzing Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 301
Work Hard to Import Data .......................................................................... 301
Design Information Systems to Produce Rich Data ................................ 302
Don\u2019t Forget about Third-Party Sources .................................................. 303
Just Add It ..................................................................................................... 303
Always Explore Descriptive Statistics ...................................................... 304
Watch for Trends ......................................................................................... 304
Slicing and Dicing: Cross-Tabulation ........................................................ 305
Chart It, Baby ............................................................................................... 305
Be Aware of Inferential Statistics ............................................................... 305
Chapter 15: Ten Tips for Visually Analyzing and
Presenting Data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 307
Using the Right Chart Type ........................................................................ 307
Using Your Chart Message as the Chart Title .......................................... 309
Beware of Pie Charts ................................................................................... 310
Consider Using Pivot Charts for Small Data Sets .................................... 310
Avoiding 3-D Charts ..................................................................................... 312
Never Use 3-D Pie Charts ............................................................................ 313
Be Aware of the Phantom Data Markers ................................................... 314
Use Logarithmic Scaling ............................................................................. 315
Don\u2019t Forget to Experiment