Analyzing Business Data with Excel

Analyzing Business Data with Excel

by Gerald Knight
Released January 2006
Publisher(s): O'Reilly Media, Inc.
ISBN: 9780596100735

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Buy on Amazon Buy on ebooks.com
Start your free trial

Book description

As one of the most widely used desktop applications ever created, Excel is familiar to just about everyone with a computer and a keyboard. Yet most of us don't know the full extent of what Excel can do, mostly because of its recent growth in power, versatility, and complexity. The truth is that there are many ways Excel can help make your job easier-beyond calculating sums and averages in a standard spreadsheet.

Analyzing Business Data with Excel shows you how to solve real-world business problems by taking Excel's data analysis features to the max. Rather than focusing on individual Excel functions and features, the book keys directly on the needs of business users. Most of the chapters start with a business problem or question, and then show you how to create pointed spreadsheets that address common data analysis issues.

Aimed primarily at experienced Excel users, the book doesn't spend much time on the basics. After introducing some necessary general tools, it quickly moves into more specific problem areas, such as the following:

  • Statistics
  • Pivot tables
  • Workload forecasting
  • Modeling
  • Measuring quality
  • Monitoring complex systems
  • Queuing
  • Optimizing
  • Importing data

If you feel as though you're getting shortchanged by your overall application of Excel, Analyzing Business Data with Excel is just the antidote. It addresses the growing Excel data analysis market head on. Accountants, managers, analysts, engineers, and supervisors-one and all-will learn how to turn Excel functionality into actual solutions for the business problems that confront them.

Publisher resources

View/Submit Errata

Table of contents

  1. A Note Regarding Supplemental Files
  2. Preface
    1. Who Should Read This Book
    2. What’s in This Book
    3. How to Use This Book Effectively
      1. Typographical Conventions
    4. Sample Code
    5. Using Code Examples
    6. We’d Like Your Feedback!
    7. Safari® Enabled
    8. Acknowledgments
  3. 1. Excel and Statistics
    1. 1.1. Array Formulas
    2. 1.2. Addressing Cells Indirectly
    3. 1.3. Statistical Functions
      1. 1.3.1. The Average
        1. 1.3.1.1. AVERAGEA
        2. 1.3.1.2. DAVERAGE
        3. 1.3.1.3. Trimmed average
        4. 1.3.1.4. Moving average
      2. 1.3.2. Changes in the Average
        1. 1.3.2.1. Growth
      3. 1.3.3. Distributions
        1. 1.3.3.1. Normal distributions
        2. 1.3.3.2. Exponential distributions
        3. 1.3.3.3. Gamma distribution
        4. 1.3.3.4. Binomial distribution
      4. 1.3.4. Correlation
  4. 2. Pivot Tables and Problem Solving
    1. 2.1. Pivot Table Basics
      1. 2.1.1. Populating the Table
      2. 2.1.2. Sorting and Filtering
      3. 2.1.3. Multiple Data Items
      4. 2.1.4. Working with Rows and Columns
      5. 2.1.5. Adding a PivotChart
      6. 2.1.6. Multiple Layers and Pages
      7. 2.1.7. Drilling Down
    2. 2.2. Changing the Data
      1. 2.2.1. Categorical Information
      2. 2.2.2. Scalar Information
    3. 2.3. Pivot Table Options
  5. 3. Workload Forecasting
    1. 3.1. The Procedure
      1. 3.1.1. Data
      2. 3.1.2. Predictions
        1. 3.1.2.1. Find the lag
        2. 3.1.2.2. Find the average
        3. 3.1.2.3. Adjust for the trend
      3. 3.1.3. Determine the Confidence Interval
      4. 3.1.4. Manage Anomalies
    2. 3.2. Building an Application
      1. 3.2.1. Design
      2. 3.2.2. List the Requirements
      3. 3.2.3. Consider the Source of Data
      4. 3.2.4. Presentation
      5. 3.2.5. Conventions and Names
      6. 3.2.6. Named Values and Ranges on Settings
      7. 3.2.7. The Named Values on the Workarea Sheet
      8. 3.2.8. Named Ranges on Workarea
      9. 3.2.9. Other Important Links on Workarea
      10. 3.2.10. Linking to the Data
      11. 3.2.11. Visual Basic
      12. 3.2.12. Formatting
      13. 3.2.13. Running the Application
      14. 3.2.14. Customizing the Application
  6. 4. Modeling
    1. 4.1. Regression
    2. 4.2. Defining the Problem
    3. 4.3. Refining Metrics
    4. 4.4. Analysis
    5. 4.5. Building the Model
    6. 4.6. Analyzing the Results
    7. 4.7. Testing Non-Linear Relationships
  7. 5. Measuring Quality
    1. 5.1. Statistical Process Control
      1. 5.1.1. Choosing Metrics
      2. 5.1.2. X and S Charts
    2. 5.2. Running the Application
    3. 5.3. Application Design
      1. 5.3.1. The Data Sheet
      2. 5.3.2. The Workarea Sheet
      3. 5.3.3. The Controls on the Display Sheet
      4. 5.3.4. Linking the Workarea Sheet to the Data Sheet
        1. 5.3.4.1. Calculations on the Workarea Sheet
      5. 5.3.5. The Display Sheet
        1. 5.3.5.1. The scroll
        2. 5.3.5.2. Other parts of the Display sheet
    4. 5.4. Customizing the Application
      1. 5.4.1. Changing the Display Sheet
      2. 5.4.2. Adding Logic
      3. 5.4.3. Adding a Macro
  8. 6. Monitoring Complex Systems
    1. 6.1. The Application
    2. 6.2. The Data
    3. 6.3. Settings
      1. 6.3.1. Independent Columns
      2. 6.3.2. The Alternate
      3. 6.3.3. The Lag
      4. 6.3.4. The Out of Limits Message
      5. 6.3.5. The Current Column
      6. 6.3.6. The Current Row
      7. 6.3.7. The Sensitivity
    4. 6.4. Workarea
      1. 6.4.1. LastRow
      2. 6.4.2. Columns Used
      3. 6.4.3. LastColumn
      4. 6.4.4. StartRow
      5. 6.4.5. IsOut
      6. 6.4.6. The Data Area
      7. 6.4.7. The Regression Area
      8. 6.4.8. The Combo Box Data Area
      9. 6.4.9. The Main Display Area
    5. 6.5. Macros
  9. 7. Queuing
    1. 7.1. The Data
    2. 7.2. The Application
    3. 7.3. The Logic
      1. 7.3.1. Using a Tag Sort on Linked Information
      2. 7.3.2. Invisible Rectangles
      3. 7.3.3. The Agent Detail Area
      4. 7.3.4. The Chart Area
      5. 7.3.5. The Timeline
      6. 7.3.6. Navigation
  10. 8. Custom Queuing Presentation
    1. 8.1. The Application
    2. 8.2. The Data
    3. 8.3. The Logic
    4. 8.4. VBA
    5. 8.5. Extending the Application
  11. 9. Optimizing
    1. 9.1. Goal Seek
      1. 9.1.1. Setting a Price
      2. 9.1.2. A Quadratic Equation
      3. 9.1.3. A Matrix Problem
      4. 9.1.4. Using Goal Seek in a Macro
    2. 9.2. The Solver
      1. 9.2.1. Finding Two Values at Once
      2. 9.2.2. Regression with the Solver
      3. 9.2.3. A Problem with Constraints
      4. 9.2.4. Zero/One Problem
      5. 9.2.5. Running the Solver with a Macro
      6. 9.2.6. Common Problems with the Solver
      7. 9.2.7. Applications
  12. 10. Importing Data
    1. 10.1. Text Files
      1. 10.1.1. Fixed Length Files
      2. 10.1.2. Delimited Files
    2. 10.2. Databases
      1. 10.2.1. Linking Tables
    3. 10.3. XML
  13. 11. The Trouble with Data
    1. 11.1. Numbers
    2. 11.2. Dates
    3. 11.3. Reports
    4. 11.4. Equivalence
  14. 12. Effective Display Techniques
    1. 12.1. Respect the Information and the Audience
    2. 12.2. Large Worksheets
    3. 12.3. Charts
    4. 12.4. Pictures and Other Objects
    5. 12.5. Complexity
    6. 12.6. Repeated Elements
    7. 12.7. Information Density
      1. 12.7.1. Support and Grouping
    8. 12.8. Emphasis and Focus
      1. 12.8.1. Metaphor
      2. 12.8.2. Fonts
      3. 12.8.3. Colors
      4. 12.8.4. Background Interaction
  15. Index
  16. About the Author
  17. Colophon
  18. Copyright

Product information

  • Title: Analyzing Business Data with Excel
  • Author(s): Gerald Knight
  • Release date: January 2006
  • Publisher(s): O'Reilly Media, Inc.
  • ISBN: 9780596100735