Excel Scientific and Engineering Cookbook
Adding Excel to Your Analysis Arsenal
Publisher: O'Reilly Media
Release Date: February 2009
Pages: 448
Read on O'Reilly Online Learning with a 10day trial
Start your free trial now Buy on AmazonWhere’s the cart? Now you can get everything with O'Reilly Online Learning. To purchase books, visit Amazon or your favorite retailer. Questions? See our FAQ or contact customer service:
18008898969 / 7078277019
support@oreilly.com
Chances are you already use Excel to perform some fairly routine calculations. Now the Excel Scientific and Engineering Cookbook shows you how to leverage Excel to perform more complex calculations, too, calculations that once fell in the domain of specialized tools. It does so by putting a smorgasbord of data analysis techniques right at your fingertips. The book shows how to perform these useful tasks and others:
 Use Excel and VBA in general
 Import data from a variety of sources
 Analyze data
 Perform calculations
 Visualize the results for interpretation and presentation
 Use Excel to solve specific science and engineering problems
Wherever possible, the Excel Scientific and Engineering Cookbook draws on realworld examples from a range of scientific disciplines such as biology, chemistry, and physics. This way, you'll be better prepared to solve the problems you face in your everyday scientific or engineering tasks.
High on practicality and low on theory, this quick, lookup reference provides instant solutions, or "recipes," to problems both basic and advanced. And like other books in O'Reilly's popular Cookbook format, each recipe also includes a discussion on how and why it works. As a result, you can take comfort in knowing that complete, practical answers are a mere pageflip away.
Table of Contents

Chapter 1 Using Excel

Introduction

Navigating the Interface

Entering Data

Setting Cell Data Types

Selecting More Than a Single Cell

Entering Formulas

Exploring the R1C1 Cell Reference Style

Referring to More Than a Single Cell

Understanding Operator Precedence

Using Exponents in Formulas

Exploring Functions

Formatting Your Spreadsheets

Defining Custom Format Styles

Leveraging Copy, Cut, Paste, and Paste Special

Using Cell Names (Like Programming Variables)

Validating Data

Taking Advantage of Macros

Adding Comments and Equation Notes

Getting Help


Chapter 2 Getting Acquainted with Visual Basic for Applications

Introduction

Navigating the VBA Editor

Writing Functions and Subroutines

Working with Data Types

Defining Variables

Defining Constants

Using Arrays

Commenting Code

Spanning Long Statements over Multiple Lines

Using Conditional Statements

Using Loops

Debugging VBA Code

Exploring VBA's Builtin Functions

Exploring Excel Objects

Creating Your Own Objects in VBA

VBA Help


Chapter 3 Collecting and Cleaning Up Data

Introduction

Importing Data from Text Files

Importing Data from Delimited Text Files

Importing Data Using DragandDrop

Importing Data from Access Databases

Importing Data from Web Pages

Parsing Data

Removing Weird Characters from Imported Text

Converting Units

Sorting Data

Filtering Data

Looking Up Values in Tables

Retrieving Data from XML Files


Chapter 4 Charting

Introduction

Creating Simple Charts

Exploring Chart Styles

Formatting Charts

Customizing Chart Axes

Setting Log or Semilog Scales

Using Multiple Axes

Changing the Type of an Existing Chart

Combining Chart Types

Building 3D Surface Plots

Preparing Contour Plots

Annotating Charts

Saving Custom Chart Types

Copying Charts to Word

Displaying Error Bars


Chapter 5 Statistical Analysis

Introduction

Computing Summary Statistics

Plotting Frequency Distributions

Calculating Confidence Intervals

Correlating Data

Ranking and Percentiles

Performing Statistical Tests

Conducting ANOVA

Generating Random Numbers

Sampling Data


Chapter 6 Time Series Analysis

Introduction

Plotting Time Series Data

Adding Trendlines

Computing Moving Averages

Smoothing Data Using Weighted Averages

Centering Data

Detrending a Time Series

Estimating Seasonal Indices

Deseasonalization of a Time Series

Forecasting

Applying Discrete Fourier Transforms


Chapter 7 Mathematical Functions

Introduction

Using Summation Functions

Delving into Division

Mastering Multiplication

Exploring Exponential and Logarithmic Functions

Using Trigonometry Functions

Seeing Signs

Getting to the Root of Things

Rounding and Truncating Numbers

Converting Between Number Systems

Manipulating Matrices

Building Support for Vectors

Using Spreadsheet Functions in VBA Code

Dealing with Complex Numbers


Chapter 8 Curve Fitting and Regression

Introduction

Performing Linear Curve Fitting Using Excel Charts

Constructing Your Own Linear Fit Using Spreadsheet Functions

Using a Single Spreadsheet Function for Linear Curve Fitting

Performing Multiple Linear Regression

Generating Nonlinear Curve Fits Using Excel Charts

Fitting Nonlinear Curves Using Solver

Assessing Goodness of Fit

Computing Confidence Intervals


Chapter 9 Solving Equations

Introduction

Finding Roots Graphically

Solving Nonlinear Equations Iteratively

Automating Tedious Problems with VBA

Solving Linear Systems

Tackling Nonlinear Systems of Equations

Using Classical Methods for Solving Equations


Chapter 10 Numerical Integration and Differentiation

Introduction

Integrating a Definite Integral

Implementing the Trapezoidal Rule in VBA

Computing the Center of an Area Using Numerical Integration

Calculating the Second Moment of an Area

Dealing with Double Integrals

Numerical Differentiation


Chapter 11 Solving Ordinary Differential Equations

Introduction

Solving FirstOrder Initial Value Problems

Applying the RungeKutta Method to SecondOrder Initial Value Problems

Tackling Coupled Equations

Shooting Boundary Value Problems


Chapter 12 Solving Partial Differential Equations

Introduction

Leveraging Excel to Directly Solve Finite Difference Equations

Recruiting Solver to Iteratively Solve Finite Difference Equations

Solving Initial Value Problems

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


Chapter 13 Performing Optimization Analyses in Excel

Introduction

Using Excel for Traditional Linear Programming

Exploring Resource Allocation Optimization Problems

Getting More Realistic Results with Integer Constraints

Tackling Troublesome Problems

Optimizing Engineering Design Problems

Understanding Solver Reports

Programming a Genetic Algorithm for Optimization


Chapter 14 Introduction to Financial Calculations

Introduction

Computing Present Value

Calculating Future Value

Figuring Out Required Rate of Return

Doubling Your Money

Determining Monthly Payments

Considering Cash Flow Alternatives

Achieving a Certain Future Value

Assessing Net Present Worth

Estimating Rate of Return

Solving Inverse Problems

Figuring a BreakEven Point


Colophon