Excel Scientific And Engineering Cookbook

For scientists and engineers who are using Excel to solve complex problems, this new Cookbook puts a smorgasbord of data analysis techniques right at their fingertips. The Excel Scientific and Engineering Cookbook shows these professionals how to leverage Excel as their primary computing tool for solving a variety of both routine and complex problems. In the famously practical style of O'Reilly's developer and administrator Cookbooks, the recipes in this new book offer a wealth of solutions to a wide range of problems faced by scientists and engineers at all levels. As a naval architect and marine engineer, David M. Bourg performs computer simulations and develops analysis tools that measure such things as hovercraft performance and the effect of waves on the motion of ships and boats. He teaches at the college level in the areas of ship design, construction and analysis.

As a naval architect and marine engineer, David M. Bourg performs computer simulations and develops analysis tools that measure such things as hovercraft performance and the effect of waves on the motion of ships and boats. He teaches at the college level in the areas of ship design, construction and analysis. On occasion, David also lectures at high schools on topics such as naval architecture and software development. In addition to David's practical engineering background, he's professionally involved in computer game development and consulting through his company, Crescent Vision Interactive.

Preface

xi

Using Excel

1

(40)

Navigating the Interface

1

(6)

Entering Data

7

(2)

Setting Cell Data Types

9

(4)

Selecting More Than a Single Cell

13

(1)

Entering Formulas

14

(5)

Exploring the R1C1 Cell Reference Style

19

(2)

Referring to More Than a Single Cell

21

(1)

Understanding Operator Precedence

21

(1)

Using Exponents in Formulas

22

(1)

Exploring Functions

22

(4)

Formatting Your Spreadsheets

26

(3)

Defining Custom Format Styles

29

(1)

Leveraging Copy, Cut, Paste, and Paste Special

30

(2)

Using Cell Names (Like Programming Variables)

32

(2)

Validating Data

34

(1)

Taking Advantage of Macros

35

(2)

Adding Comments and Equation Notes

37

(3)

Getting Help

40

(1)

Getting Acquainted with Visual Basic for Applications

41

(32)

Navigating the VBA Editor

42

(3)

Writing Functions and Subroutines

45

(4)

Working with Data Types

49

(1)

Defining Variables

50

(2)

Defining Constants

52

(1)

Using Arrays

53

(1)

Commenting Code

54

(1)

Spanning Long Statements over Multiple Lines

55

(1)

Using Conditional Statements

55

(1)

Using Loops

56

(3)

Debugging VBA Code

59

(3)

Exploring VBA's Built-in Functions

62

(2)

Exploring Excel Objects

64

(5)

Creating Your Own Objects in VBA

69

(3)

VBA Help

72

(1)

Collecting and Cleaning Up Data

73

(35)

Importing Data from Text Files

73

(5)

Importing Data from Delimited Text Files

78

(1)

Importing Data Using Drag-and-Drop

79

(1)

Importing Data from Access Databases

80

(3)

Importing Data from Web Pages

83

(1)

Parsing Data

84

(2)

Removing Weird Characters from Imported Text

86

(1)

Converting Units

87

(3)

Sorting Data

90

(3)

Filtering Data

93

(4)

Looking Up Values in Tables

97

(8)

Retrieving Data from XML Files

105

(3)

Charting

108

(37)

Creating Simple Charts

108

(8)

Exploring Chart Styles

116

(1)

Formatting Charts

117

(1)

Customizing Chart Axes

118

(3)

Setting Log or Semilog Scales

121

(2)

Using Multiple Axes

123

(5)

Changing the Type of an Existing Chart

128

(2)

Combining Chart Types

130

(1)

Building 3D Surface Plots

131

(6)

Preparing Contour Plots

137

(2)

Annotating Charts

139

(3)

Saving Custom Chart Types

142

(1)

Copying Charts to Word

142

(1)

Displaying Error Bars

143

(2)

Statistical Analysis

145

(27)

Computing Summary Statistics

146

(4)

Plotting Frequency Distributions

150

(4)

Calculating Confidence Intervals

154

(1)

Correlating Data

155

(5)

Ranking and Percentiles

160

(2)

Performing Statistical Tests

162

(4)

Conducting ANOVA

166

(2)

Generating Random Numbers

168

(1)

Sampling Data

169

(3)

Time Series Analysis

172

(43)

Plotting Time Series Data

172

(1)

Adding Trendlines

173

(2)

Computing Moving Averages

175

(7)

Smoothing Data Using Weighted Averages

182

(6)

Centering Data

188

(3)

Detrending a Time Series

191

(4)

Estimating Seasonal Indices

195

(4)

Deseasonalization of a Time Series

199

(2)

Forecasting

201

(3)

Applying Discrete Fourier Transforms

204

(11)

Mathematical Functions

215

(18)

Using Summation Functions

215

(1)

Delving into Division

216

(1)

Mastering Multiplication

217

(2)

Exploring Exponential and Logarithmic Functions

219

(2)

Using Trigonometry Functions

221

(1)

Seeing Signs

222

(1)

Getting to the Root of Things

222

(1)

Rounding and Truncating Numbers

223

(1)

Converting Between Number Systems

224

(1)

Manipulating Matrices

225

(2)

Building Support for Vectors

227

(3)

Using Spreadsheet Functions in VBA Code

230

(1)

Dealing with Complex Numbers

230

(3)

Curve Fitting and Regression

233

(30)

Performing Linear Curve Fitting Using Excel Charts

233

(4)

Constructing Your Own Linear Fit Using Spreadsheet Functions

237

(4)

Using a Single Spreadsheet Function for Linear Curve Fitting

241

(3)

Performing Multiple Linear Regression

244

(3)

Generating Nonlinear Curve Fits Using Excel Charts

247

(2)

Fitting Nonlinear Curves Using Solver

249

(5)

Assessing Goodness of Fit

254

(5)

Computing Confidence Intervals

259

(4)

Solving Equations

263

(33)

Finding Roots Graphically

271

(2)

Solving Nonlinear Equations Iteratively

273

(4)

Automating Tedious Problems with VBA

277

(7)

Solving Linear Systems

284

(5)

Tackling Nonlinear Systems of Equations

289

(2)

Using Classical Methods for Solving Equations

291

(5)

Numerical Integration and Differentiation

296

(22)

Integrating a Definite Integral

297

(4)

Implementing the Trapezoidal Rule in VBA

301

(3)

Computing the Center of an Area Using Numerical Integration

304

(3)

Calculating the Second Moment of an Area

307

(1)

Dealing with Double Integrals

308

(3)

Numerical Differentiation

311

(7)

Solving Ordinary Differential Equations

318

(24)

Solving First-Order Initial Value Problems

318

(7)

Applying the Runge-Kutta Method to Second-Order Initial Value Problems

325

(5)

Tackling Coupled Equations

330

(7)

Shooting Boundary Value Problems

337

(5)

Solving Partial Differential Equations

342

(17)

Leveraging Excel to Directly Solve Finite Difference Equations

344

(2)

Recruiting Solver to Iteratively Solve Finite Difference Equations

346

(5)

Solving Initial Value Problems

351

(4)

Using Excel to Help Solve Problems Formulated Using the Finite Element Method

355

(4)

Performing Optimization Analyses in Excel

359

(42)

Using Excel for Traditional Linear Programming

359

(4)

Exploring Resource Allocation Optimization Problems

363

(5)

Getting More Realistic Results with Integer Constraints

368

(2)

Tackling Troublesome Problems

370

(6)

Optimizing Engineering Design Problems

376

(4)

Understanding Solver Reports

380

(5)

Programming a Genetic Algorithm for Optimization

385

(16)

Introduction to Financial Calculations

401

(14)

Computing Present Value

402

(1)

Calculating Future Value

402

(1)

Figuring Out Required Rate of Return

403

(1)

Doubling Your Money

404

(1)

Determining Monthly Payments

404

(1)

Considering Cash Flow Alternatives

405

(2)

Achieving a Certain Future Value

407

(1)

Assessing Net Present Worth

408

(2)

Estimating Rate of Return

410

(1)

Solving Inverse Problems

411

(1)

Figuring a Break-Even Point

412

(3)

Index

415

What is included with this book?

The New copy of this book will include any supplemental materials advertised. Please check the title of the book to determine if it should include any access cards, study guides, lab manuals, CDs, etc.

The Used, Rental and eBook copies of this book are not guaranteed to include any supplemental materials. Typically, only the book itself is included. This is true even if the title states it includes any access cards, study guides, lab manuals, CDs, etc.

FREE SHIPPING

Amazon no longer offers textbook rentals. We do!

Excel Scientific And Engineering Cookbook

Summary

Author Biography

Table of Contents

Supplemental Materials