Analyzing Business Data with Excel

Preface

Excel is everywhere, one of the most widely used desktop applications ever created. Over the years its power and versatility have grown, and so has its complexity. Today, most Excel users do not know all the things that it can do. There are plenty of good books that explain functions and features, but making Excel solve problems is not just a matter of learning new workbook functions or mastering Visual Basic. The real challenge is to understand what Excel’s many features can accomplish, and especially how you can combine them to make your job easier.

Most of the chapters in this book start with a business problem or question, and then show how Excel can be used in that situation. Several of the chapters include complete applications that you can use and modify as you like. Each solution basically shows the reader how I would handle the problem. In nearly every case, Excel offers many ways to do things so the solutions presented are not the only option. You could produce solutions that do the same thing as the ones in this book using a different approach. This is not a manual and it doesn’t include every workbook function. The object is to show the reader what can be done and to explain at least one way to do it.

Who Should Read This Book

This book is written for experienced Excel users. It doesn’t spend much time on basics and assumes the reader already knows how Excel works. If you are starting from scratch or need a comprehensive manual, you might consider Excel: The Missing Manual, also from O’Reilly.

If you are already comfortable with Excel and would like to see how some of the more advanced features are used, this is the book for you.

What’s in This Book

This book starts with some necessary general tools and then moves into more specific problem areas.

Chapter 1, Excel and Statistics

Covers averages, trends, correlation, distributions, and array formulas.

Chapter 2, Pivot Tables and Problem Solving

Examines pivot table basics and ways to modify data to make it work better with pivot tables.

Chapter 3, Workload Forecasting

Covers the application development process, worksheet organization, and forecasting techniques. This chapter includes an application that forecasts a typical workload.

Chapter 4, Modeling

Explores regression, problem definition, analysis, model construction, and interpretation of results.

Chapter 5, Measuring Quality

Works with statistical process control, X and Y charts, and application design. This chapter includes an application that uses statistical process control to measure quality in an operation.

Chapter 6, Monitoring Complex Systems

Examines data requirements, statistical techniques and logic, application design, and organization. This chapter includes an application that uses regression to monitor the relationship between metrics in a complex business process.

Chapter 7, Queuing

Applies formatting, VBA, and logic in an application that measures worker performance in a queuing operation.

Chapter 8, Custom Queuing Presentation

Continues the discussion of queuing with another application focusing on the status of the queue.

Chapter 9, Optimizing

Explains how to use Goal Seek and Solver for various kinds of problems.

Chapter 10, Importing Data

Covers importing from text files, databases, and XML.

Chapter 11, The Trouble with Data

Examines common problems with dates, numeric information, dealing with data in report form, and equivalence problems.

Chapter 12, Effective Display Techniques

Covers display design, color combination, dealing with complexity, and visual considerations.

How to Use This Book Effectively

This book covers most of Excel’s advanced features. If you are interested in specific features, this section will guide you to the information you are looking for:

Feature	Chapter
VBA in code modules	3
VBA in a sheet	8
Build a custom function	11
Building an add-on	1
Code to work with text	11
Regression in analysis	4
Regression in an application	6
Using pivot tables	2
Design conventions	3
Goal Seek and Solver	9

Typographical Conventions

The following typographical conventions are used in this book:

Italic

Introduces new terms and indicates URLs, commands, file extensions, filenames, directory or folder names, and UNC pathnames.

Constant width

Indicates command-line elements, computer output, code examples, methods, variables, functions, properties, objects, events, statements, procedures, values, loops, and formulas formatted as equations.

Constant width italic

Indicates placeholders (for which you substitute an actual name) in examples and in registry keys.

Constant width bold

Indicates user input.

Tip

Indicates a tip, suggestion, or general note.

Warning

Indicates a warning or caution.

Sample Code

All of the applications for this book are available at http://www.oreilly.com/catalog/analyzingbdwe. You’ll probably want to download them and follow along with the chapters. You can also customize them to analyze your own business data.

Using Code Examples

This book is here to help you get your job done. In general, you may use the code in this book in your programs and documentation. You do not need to contact O’Reilly for permission unless you’re reproducing a significant portion of the code. For example, writing a program that uses several chunks of code from this book does not require permission. Selling or distributing a CD-ROM of examples from O’Reilly books does require permission. Answering a question by citing this book and quoting example code does not require permission. Incorporating a significant amount of example code from this book into your product’s documentation does require permission.

We appreciate, but do not require, attribution. An attribution usually includes the title, author, publisher, and ISBN. For example: Analyzing Business Data with Excel, by Gerald Knight. Copyright 2006 O’Reilly Media, Inc., 0-596-10073-6.

If you feel your use of code examples falls outside fair use or the permission given above, feel free to contact us at [e-mail].

We’d Like Your Feedback!

The information in this book has been tested and verified to the best of our ability, but mistakes and oversights do occur. Please let us know about errors you may find, as well as your suggestions for future editions, by writing to:

O’Reilly Media, Inc.

1005 Gravenstein Highway North

Sebastopol, CA 95472

800-998-9938 (in the U.S. or Canada)

707-829-0515 (international or local)

707-829-0104 (fax)

You also can send us messages using email. To be put on our mailing list or to request a catalog, send email to:

[e-mail]

To ask technical questions or comment on the book, send email to:

[e-mail]

For corrections and amplifications to this book, check out O’Reilly Media’s online catalog at:

http://www.oreilly.com/catalog/analyzingbdwe

Safari^® Enabled

When you see a Safari® Enabled icon on the cover of your favorite technology book, it means the book is available online through the O’Reilly Network Safari Bookshelf.

Safari offers a solution that’s better than e-books. It’s a virtual library that lets you easily search thousands of top technology books, cut and paste code samples, download chapters, and find quick answers when you need the most accurate, current information. Try it for free at http://safari.oreilly.com.

Acknowledgments

Thanks to O’Reilly for the opportunity to write the book and to my editor Simon St.Laurent for guidance and encouragement along the way.

Thanks to Michael Schmalz for suggestions (all good) and for keeping me out of trouble.

And finally to my wife Betty and daughter Helen: thanks for putting up with me during the last few months. It’s safe to come back into the computer room now.