book

Excel Data Analysis For Dummies, 2nd Edition

Name: Excel Data Analysis For Dummies, 2nd Edition
ISBN: 9781118898093

by E. C. Nelson, Stephen L. Nelson

May 2014

Beginner

360 pages

7h 37m

English

For Dummies

Read now

Unlock full access

Introduction
About This BookWhat You Can Safely IgnoreWhat You Shouldn’t Ignore (Unless You’re a Masochist)Foolish AssumptionsHow This Book Is OrganizedPart I: Where’s the Beef?Part II: PivotTables and PivotChartsPart III: Advanced ToolsPart IV: The Part of TensIcons Used in This BookBeyond the BookWhere to Go from Here
Part I: Where's the Beef?
Chapter 1: Introducing Excel Tables
What Is a Table and Why Do I Care?Building TablesExporting from a databaseBuilding a table the hard wayBuilding a table the semi-hard wayAnalyzing Table InformationSimple statisticsSorting table recordsUsing AutoFilter on a tableUndoing a filterTurning off filterUsing the custom AutoFilterFiltering a filtered tableUsing advanced filtering
Chapter 2: Grabbing Data from External Sources
Getting Data the Export-Import WayExporting: The first stepImporting: The second step (if necessary)Querying External Databases and Web Page TablesRunning a web queryImporting a database tableQuerying an external databaseIt's Sometimes a Raw Deal
Chapter 3: Scrub-a-Dub-Dub: Cleaning Data
Editing Your Imported WorkbookDelete unnecessary columnsDelete unnecessary rowsResize columnsResize rowsErase unneeded cell contentsFormat numeric valuesCopying worksheet dataMoving worksheet dataReplacing data in fieldsCleaning Data with Text FunctionsWhat’s the big deal, Steve?The answer to some of your problemsThe CLEAN functionThe CONCATENATE functionThe EXACT functionThe FIND functionThe FIXED functionThe LEFT functionThe LEN functionThe LOWER functionThe MID functionThe PROPER functionThe REPLACE functionThe REPT functionThe RIGHT functionThe SEARCH functionThe SUBSTITUTE functionThe T functionThe TEXT functionThe TRIM functionThe UPPER functionThe VALUE functionConverting text function formulas to textUsing Validation to Keep Data Clean
Part II: PivotTables and PivotCharts
Chapter 4: Working with PivotTables
Looking at Data from Many AnglesGetting Ready to PivotRunning the PivotTable WizardFooling Around with Your Pivot TablePivoting and re-pivotingFiltering pivot table dataRefreshing pivot table dataSorting pivot table dataPseudo-sortingGrouping and ungrouping data itemsSelecting this, selecting thatWhere did that cell’s number come from?Setting value field settingsCustomizing How Pivot Tables Work and LookSetting pivot table optionsFormatting pivot table information
Chapter 5: Building PivotTable Formulas
Adding Another Standard CalculationCreating Custom CalculationsUsing Calculated Fields and ItemsAdding a calculated fieldAdding a calculated itemRemoving calculated fields and itemsReviewing calculated field and calculated item formulasReviewing and changing solve orderRetrieving Data from a Pivot TableGetting all the values in a pivot tableGetting a value from a pivot tableArguments of the GETPIVOTDATA function
Chapter 6: Working with PivotCharts
Why Use a Pivot Chart?Getting Ready to PivotRunning the PivotTable WizardFooling Around with Your Pivot ChartPivoting and re-pivotingFiltering pivot chart dataRefreshing pivot chart dataGrouping and ungrouping data itemsUsing Chart Commands to Create Pivot Charts
Chapter 7: Customizing PivotCharts
Selecting a Chart TypeWorking with Chart StylesChanging Chart LayoutChart and axis titlesChart legendChart data labelsChart data tablesChart axesChart gridlinesChanging a Chart’s LocationFormatting the Plot AreaFormatting the Chart AreaChart fill patternsChart area fontsFormatting 3-D ChartsFormatting the walls of a 3-D chartUsing the 3-D View command

Part III: Advanced Tools
Chapter 8: Using the Database Functions
Quickly Reviewing FunctionsUnderstanding function syntax rulesEntering a function manuallyEntering a function with the Function commandUsing the DAVERAGE FunctionUsing the DCOUNT and DCOUNTA FunctionsUsing the DGET FunctionUsing the DMAX and DMAX FunctionsUsing the DPRODUCT FunctionUsing the DSTDEV and DSTDEVP FunctionsUsing the DSUM FunctionUsing the DVAR and DVARP Functions
Chapter 9: Using the Statistics Functions
Counting Items in a Data SetCOUNT: Counting cells with valuesCOUNTA: Alternative counting cells with valuesCOUNTBLANK: Counting empty cellsCOUNTIF: Counting cells that match criteriaPERMUT: Counting permutationsCOMBIN: Counting combinationsMeans, Modes, and MediansAVEDEV: An average absolute deviationAVERAGE: AverageAVERAGEA: An alternate averageTRIMMEAN: Trimming to a meanMEDIAN: Median valueMODE: Mode valueGEOMEAN: Geometric meanHARMEAN: Harmonic meanFinding Values, Ranks, and PercentilesMAX: Maximum valueMAXA: Alternate maximum valueMIN: Minimum valueMINA: Alternate minimum valueLARGE: Finding the kth largest valueSMALL: Finding the kth smallest valueRANK: Ranking an array valuePERCENTRANK: Finding a percentile rankingPERCENTILE: Finding a percentile rankingFREQUENCY: Frequency of values in a rangePROB: Probability of valuesStandard Deviations and VariancesSTDEV: Standard deviation of a sampleSTDEVA: Alternate standard deviation of a sampleSTDEVP: Standard deviation of a populationSTDEVPA: Alternate standard deviation of a populationVAR: Variance of a sampleVARA: Alternate variance of a sampleVARP: Variance of a populationVARPA: Alternate variance of a populationCOVARIANCE.P and COVARIANCE.S: CovariancesDEVSQ: Sum of the squared deviationsNormal DistributionsNORM.DIST: Probability X falls at or below a given valueNORM.INV: X that gives specified probabilityNORM.S.DIST: Probability variable within z-standard deviationsNORM.S.INV: z-value equivalent to a probabilitySTANDARDIZE: z-value for a specified valueCONFIDENCE: Confidence interval for a population meanKURT: KurtosisSKEW and SKEW.P: Skewness of a distributiont-distributionsT.DIST: Left-tail Student t-distributionT.DIST.RT: Right-tail Student t-distributionT.DIST.2T: Two-tail Student t-distributionT.INV: Left-tailed Inverse of Student t-distributionT.INV.2T: Two-tailed Inverse of Student t-distributionT.TEST: Probability two samples from same populationf-distributionsF.DIST: Left-tailed f-distribution probabilityF.DIST.RT: Right-tailed f-distribution probabilityF.INV:Left-tailed f-value given f-distribution probabilityF.INV.RT:Right-tailed f-value given f-distribution probabilityF.TEST: Probability data set variances not differentBinomial DistributionsBINOM.DIST: Binomial probability distributionBINOM.INV: Binomial probability distributionBINOM.DIST.RANGE: Binomial probability of Trial ResultNEGBINOM.DIST: Negative binominal distributionCRITBINOM: Cumulative binomial distributionHYPGEOM.DIST: Hypergeometric distributionChi-Square DistributionsCHISQ.DIST.RT: Chi-square distributionCHISQ.DIST: Chi-square distributionCHISQ.INV.RT: Right-tailed chi-square distribution probabilityCHISQ.INV: Left-tailed chi-square distribution probabilityCHISQ.TEST: Chi-square testRegression AnalysisFORECAST: Forecast dependent variables using a best-fit lineINTERCEPT: y-axis intercept of a lineLINESTSLOPE: Slope of a regression lineSTEYX: Standard errorTRENDLOGEST: Exponential regressionGROWTH: Exponential growthCorrelationCORREL: Correlation coefficientPEARSON: Pearson correlation coefficientRSQ: r-squared value for a Pearson correlation coefficientFISHERFISHERINVSome Really Esoteric Probability DistributionsBETA.DIST: Cumulative beta probability densityBETA.INV: Inverse cumulative beta probability densityEXPON.DIST: Exponential probability distributionGAMMA.DIST: Gamma distribution probabilityGAMMAINV: X for a given gamma distribution probabilityGAMMALN: Natural logarithm of a gamma distributionLOGNORMDIST: Probability of lognormal distributionLOGINV: Value associated with lognormal distribution probabilityPOISSON: Poisson distribution probabilitiesWEIBULL: Weibull distributionZTEST: Probability of a z-test
Chapter 10: Descriptive Statistics
Using the Descriptive Statistics ToolCreating a HistogramRanking by PercentileCalculating Moving AveragesExponential SmoothingGenerating Random NumbersSampling Data
Chapter 11: Inferential Statistics
Using the t-test Data Analysis ToolPerforming z-test CalculationsCreating a Scatter PlotUsing the Regression Data Analysis ToolUsing the Correlation Analysis ToolUsing the Covariance Analysis ToolUsing the ANOVA Data Analysis ToolsCreating an f-test AnalysisUsing Fourier Analysis
Chapter 12: Optimization Modeling with Solver
Understanding Optimization ModelingOptimizing your imaginary profitsRecognizing constraintsSetting Up a Solver WorksheetSolving an Optimization Modeling ProblemReviewing the Solver ReportsThe Answer ReportThe Sensitivity ReportThe Limits ReportSome other notes about Solver reportsWorking with the Solver OptionsUsing the All Methods optionsUsing the GRG Nonlinear tabUsing the Evolutionary tabSaving and reusing model informationUnderstanding the Solver Error MessagesSolver has found a solutionSolver has converged to the current solutionSolver cannot improve the current solutionStop chosen when maximum time limit was reachedSolver stopped at user’s requestStop chosen when maximum iteration limit was reachedObjective Cell values do not convergeSolver could not find a feasible solutionLinearity conditions required by this LP Solver are not satisfiedThe problem is too large for Solver to handleSolver encountered an error value in a target or constraint cellThere is not enough memory available to solve the problemError in model. Please verify that all cells and constraints are valid
Part IV: The Part of Tens
Chapter 13: Ten Things You Ought to Know about Statistics
Descriptive Statistics Are StraightforwardAverages Aren’t So Simple SometimesStandard Deviations Describe DispersionAn Observation Is an ObservationA Sample Is a Subset of ValuesInferential Statistics Are Cool but ComplicatedProbability Distribution Functions Aren't Always ConfusingUniform distributionNormal distributionParameters Aren't So ComplicatedSkewness and Kurtosis Describe a Probability Distribution’s ShapeConfidence Intervals Seem Complicated at First, but Are Useful
Chapter 14: Almost Ten Tips for Presenting Table Results and Analyzing Data
Work Hard to Import DataDesign Information Systems to Produce Rich DataDon’t Forget about Third-Party SourcesJust Add ItAlways Explore Descriptive StatisticsWatch for TrendsSlicing and Dicing: Cross-TabulationChart It, BabyBe Aware of Inferential Statistics
Chapter 15: Ten Tips for Visually Analyzing and Presenting Data
Using the Right Chart TypeUsing Your Chart Message as the Chart TitleBeware of Pie ChartsConsider Using Pivot Charts for Small Data SetsAvoiding 3-D ChartsNever Use 3-D Pie ChartsBe Aware of the Phantom Data MarkersUse Logarithmic ScalingDon’t Forget to ExperimentGet Tufte
Appendix: Glossary of Data Analysis and Excel Terms
About the Authors
Cheat Sheet
More Dummies Products

404

Not Found

Either this content does not exist or you do not have access to it. Try searching for something new.

Cloud Computing

Data Engineering

Data Science

AI & ML

Programming Languages

Software Architecture

IT/Ops

Security

Design

Business

Soft Skills

Excel Data Analysis For Dummies, 2nd Edition

by E. C. Nelson, Stephen L. Nelson

404

Not Found