Excel Scientific and Engineering Cookbook

Preface

The purpose of this book is to show you how to leverage Excel as a capable computing tool to solve a variety of both routine and complex problems. As a practicing engineer and researcher, I’ve used Excel extensively for everything from routine data analysis to sophisticated design optimization problems. There’s little doubt Excel spreadsheets are quick and easy to use. Excel’s convenient spreadsheet interface and functionality combined with its integrated Visual Basic for Applications programming language make it a powerful, versatile computing tool. Its ready availability makes it even more attractive.

It’s tough to dismiss Excel as a computing tool when it’s very likely sitting right there on your work and home computers. You probably already use Excel to some extent for routine calculations. I’d like to show you how you can leverage Excel for some calculations that are not so routine. Don’t worry if you’re new to Excel, because I cover the basics as well.

This book if full of real-world uses of Excel, many of which I have personally performed either for my own work or for clients. Throughout this book, I try to use easily recognizable, yet real-world, examples wherever possible. To this end, I draw examples from a variety of scientific disciplines, ranging from biology and chemistry to physics to structural engineering. I hope to show you how to leverage Excel to solve problems you may face in your everyday scientific or engineering tasks.

1. Who Should Read This Book

This book is for anyone who has scientific or engineering calculations to perform.

If you are a science or engineering student, then this book is for you. As a student and instructor myself, I know that sometimes you face a lack of courses designed to give you real-world computing experience, leaving you to fend for yourself. Or you are taught how to use computing tools that are available only in special labs on campus. My personal experience suggests that students often gravitate toward the most readily accessible tool, which in many cases is Excel. You probably have Excel on your home computer, your parents’ computer, or at your place of work. The trouble you often face, however, is that most resources on Excel don’t address the particular types of problems you as a science or engineering student must solve; the vast majority of Excel books deal with financial calculations.

If you are already a veteran scientist or engineer who’s used to using programming languages such as Fortran and are looking for an alternative, then this book is for you too. I personally know several professionals who are looking at Excel to replace, to some extent, Fortran and other computing tools such as Mathcad and Mathematica. One of the primary reservations these scientists and engineers have with migrating to Excel is they feel Excel is not powerful enough to solve the sort of problems they deal with. I hope to dispel some of those reservations with this book.

Finally, if you are a scientist or engineer who has already embraced Excel as your primary computing tool but aren’t yet harnessing its full computing potential, then this book is also for you. As a professional you probably don’t have time to wade through disparate Excel and VBA books to piece together the information you need to solve specific problems. This book provides a convenient, concise guide focused on scientific and engineering problems. The recipe format of this book allows you to quickly find the type of problem you’re solving and get the answer without having to read the entire book sequentially. Moreover, I provide many cross-references throughout the book to point you toward relevant background material.

2. What’s in This Book

This book aims to present Excel as a “cradle to grave” computing environment for scientific and engineering computations. What I mean by this is that I want to show how to use Excel and VBA in general, how to import data from a variety of sources, analyze data, perform calculations, and finally visualize the results for interpretation and presentation. Further, since I’m assuming you have limited time for learning a computing language, I show you in a very concise way how to immediately use Excel to solve science and engineering problems. In order to demonstrate Excel’s power and versatility, I show you how to solve some relatively complicated problems that are not immediately recognizable as suitable problems for Excel.

This book consists of 14 chapters. Here is a brief overview of each chapter:

Chapter 1, Using Excel

Is meant for those new to Excel. It covers a variety of fundamental topics, including navigating Excel’s interface, entering data and formulas, and formatting cells. Experienced Excel users can safely skip this chapter.

Chapter 2, Getting Acquainted with Visual Basic for Applications

Is also meant for those new to Excel’s Visual Basic for Applications (VBA) features. This chapter covers a variety of fundamental VBA programming topics, including VBA syntax, using the VBA editor, and integrating VBA code with Excel spreadsheets. Experienced VBA users can safely skip this chapter.

Chapter 3, Collecting and Cleaning Up Data

Shows you how to import data into Excel from a variety of sources, including text files, databases, and the Web.

Chapter 4, Charting

Shows you how to visualize data in Excel. This chapter shows a variety of example charts, including line, bar, 3D surface, and combination charts.

Chapter 5, Statistical Analysis

Shows you how to perform standard statistical calculations in Excel, such as computing summary statistics, calculating confidence intervals, and performing analysis of variance, among other calculations.

Chapter 6, Time Series Analysis

Is an extension of the data analysis topics covered in Chapter 5, but specifically focusing on conducting various time series analysis tasks, including forecasting and Fourier transforms in Excel.

Chapter 7, Mathematical Functions

Introduces you to many of Excel’s useful math functions.

Chapter 8, Curve Fitting and Regression

Continues discussion of data analysis, specifically focusing on curve fitting and regression analysis.

Chapter 9, Solving Equations

Shows you how to leverage Excel to solve linear and nonlinear equations, as well as linear and nonlinear systems.

Chapter 10, Numerical Integration and Differentiation

Shows you how to perform various numerical integration calculations, such as computing areas and moments of areas, among others. This chapter also shows you how to perform numerical differentiation using standard difference formulas and how to improve the results.

Chapter 11, Solving Ordinary Differential Equations

Shows you how to solve initial value and boundary value problems using standard numerical techniques such as the Runge-Kutta and shooting methods.

Chapter 12, Solving Partial Differential Equations

Shows you how to leverage Excel to help solve problems involving partial differential equations.

Chapter 13, Performing Optimization Analyses in Excel

Shows you how to perform optimization analysis using Excel’s built-in optimization tools. This chapter also shows you how to implement a not so standard method—a genetic algorithm—using VBA.

Chapter 14, Introduction to Financial Calculations

Introduces you to some of Excel’s extensive financial analysis capabilities.

3. Conventions in This Book

The following typographical conventions are used in this book:

Constant width

Indicates VBA and Excel functions; spreadsheet formulas and cell data; user-assigned names for cells, rows, and columns; and other literals. Also used for code exampes and for text that you type in literally.

Constant width italic

Indicates placeholders (for which you substitute an actual name) in examples, cell formulas, and Excel function calls.

Constant width bold

Used to highlight segments of code.

Italic

Used for URLs, file extensions, filenames, and directory names. Also used for emphasis and to introduce new terms.

Tip

Indicates a tip, suggestion, or general note.

4. 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 us 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 doesn’t 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: "Excel Scientific and Engineering Cookbook, by David M. Bourg. Copyright 2006 O’Reilly Media, Inc., 0-596-00879-1.”

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

5. Safari Enabled

When you see a Safari® Enabled icon on the cover of your favorite technology book, that 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 tech 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.

6. 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)

We have a web page for this book, where we list errata, examples, and any additional information. You can access this page at:

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

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

[e-mail]

For more information about our books, conferences, Resource Centers, and the O’Reilly Network, see our web site at:

http://www.oreilly.com

7. Acknowledgments

Thanks to O’Reilly for taking on this project with me. This is my third book project with O’Reilly, and I can say the staff at O’Reilly are true professionals and a joy to work with. Special thanks go to my editor, Simon St. Laurent, for his skillful editing and flexibility in allowing me to pursue my vision for this book.

I want to extend thanks to the respectable Dr. Dongming Wei at the University of New Orleans for his thoughtful technical review. And thanks to my friend and Excel power user extraordinaire Kenneth Humphreys for his meticulous review and suggestions.

Finally, I want to dedicate this book to my lovely wife, Helena, and beautiful daughter, Natalia, and thank them for their unwavering support.