Excel Data Analysis For Dummies. Paul McFedries
rel="nofollow" href="#u67e2482a-0f80-575f-9746-211b414f6a1f">Part 4: The Part of Tens Chapter 14: Ten Things You Ought to Know about Statistics Descriptive Statistics Are Straightforward Averages Aren’t So Simple Sometimes Standard Deviations Describe Dispersion An Observation Is an Observation A Sample Is a Subset of Values Inferential Statistics Are Cool But Complicated Probability Distributions Aren't Always Confusing Parameters Aren't So Complicated Skewness and Kurtosis Describe a Probability Distribution’s Shape Confidence Intervals Seem Complicated at First But Are Useful Chapter 15: Ten Ways to Analyze Financial Data Calculating Future Value Calculating Present Value Determining Loan Payments Calculating a Loan Payment's Principal and Interest Calculating Cumulative Loan Principal and Interest Finding the Required Interest Rate Determining the Internal Rate of Return Calculating Straight-Line Depreciation Returning the Fixed-Declining Balance Depreciation Determining the Double-Declining Balance Depreciation Chapter 16: Ten Ways to Raise Your PivotTable Game Turn the PivotTable Fields Task Pane On and Off Change the PivotTable Fields Task Pane Layout Display the Details Behind PivotTable Data Apply a PivotTable Style Create a Custom PivotTable Style Preserve PivotTable Formatting Rename the PivotTable Turn Off Grand Totals Reduce the Size of PivotTable Workbooks Use a PivotTable Value in a Formula
9 Appendix: Glossary of Data Analysis and Excel Terms
10 Index
11 Author Bio
List of Tables
1 Chapter 3TABLE 3-1 Quick Statistical Measures Available on the Status BarTABLE 3-2 Excel’s Comparison Operators
2 Chapter 12TABLE 12-1 Measures Generated by the Descriptive Statistics ToolTABLE 12-2 The Rank and Percentile Tool’s Output Columns
List of Illustrations
1 Chapter 1FIGURE 1-1: The Greater Than dialog box and some highlighted values.FIGURE 1-2: Use the Duplicate Values rule to highlight worksheet duplicates.FIGURE 1-3: The Top 10 Items dialog box with the top 5 values highlighted.FIGURE 1-4: The higher the value, the longer the data bar.FIGURE 1-5: Excel applies an icon based on each cell’s value.FIGURE 1-6: Use the New Formatting Rule dialog box to create a custom rule.FIGURE 1-7: Use the Conditional Formatting Rules Manager to edit your rules.FIGURE 1-8: Use the Subtotal dialog box to apply subtotals to a range.FIGURE 1-9: Some subtotals applied to the Total column for each customer.FIGURE 1-10: When you group a range, Excel displays its outlining tools.FIGURE 1-11: Consolidate multiple worksheets by adding a range from each one.FIGURE 1-12: When consolidating by category, tell Excel where your labels are l...
2 Chapter 2FIGURE 2-1: This data table has the input values in a column.FIGURE 2-2: Enter the address of the input cell.FIGURE 2-3: The data table results.FIGURE 2-4: For a two-input data table, enter one set of values in a column and...FIGURE 2-5: Enter the addresses of the input cells.FIGURE 2-6: The two-input data table results.FIGURE 2-7: Using Goal Seek to calculate the annual deposit required to end up ...FIGURE 2-8: Goal Seek took all of a second or two to find a solution.FIGURE 2-9: Creating a scenario for a mortgage analysis.FIGURE 2-10: Example values for a scenario’s changing cells.FIGURE 2-11: The goal for this data model is to find the break-even point (wher...FIGURE 2-12: The completed Solver Parameters dialog box.FIGURE 2-13: The Solver Results dialog box and the solution to the break-even p...FIGURE 2-14: The completed Add Constraint dialog box.FIGURE 2-15: The Solver Results dialog box and the final solution to the break-...
3 Chapter 3FIGURE 3-1: Some table terminology you should know.FIGURE 3-2: Excel takes a stab at the full range you want to convert.FIGURE 3-3: The range is now a full-fledged table, just like that.FIGURE 3-4: Select a column’s cells, and Excel displays a few stats in the stat...FIGURE 3-5: Excel adds a Total row to the bottom of the table and displays the ...FIGURE 3-6: Selecting the Quantity column’s Sort & Filter button displays this ...FIGURE 3-7: The Sort dialog box set up for a two-level sort.FIGURE 3-8: Select the check box beside each column value that you want to incl...FIGURE 3-9: The Parts table filtered to show only the records from Division 3.FIGURE 3-10: The Custom AutoFilter dialog box.FIGURE 3-11: The filter shown in Figure 3-10 applied to the Parts table.FIGURE 3-12: A table set up for an advanced filter.FIGURE 3-13: Put the comparison expressions on one row to match them all.FIGURE 3-14: Put the comparison expressions on separate rows to match one or mo...FIGURE 3-15: An advanced filter all set to go.FIGURE 3-16: The results of the advanced filter in Figure 3-15.
4 Chapter 4FIGURE 4-1: Select a table or query in the Navigator dialog box to see a previe...FIGURE 4-2: Select the delimiter that gets the data into nice, neat columns.FIGURE 4-3: If needed, edit the column starting points until your columns are c...FIGURE 4-4: Choose the method you want to use to access the web data.FIGURE 4-5: Select a table to preview its data.FIGURE 4-6: Select an XML table to preview its data.FIGURE