Excel Scientific and Engineering Cookbook

book cover
Excel Scientific and Engineering Cookbook
By David M. Bourg
Publisher: O'Reilly
Pub Date: January 2006
Print ISBN-10: 0-596-00879-1
Print ISBN-13: 978-0-59-600879-6
Pages: 442

Table of Contents  | Index

Given the improved analytical capabilities of Excel, scientists and engineers everywhere are using it--instead of FORTRAN--to solve problems. And why not? Excel is installed on millions of computers, features a rich set of built-in analyses tools, and includes an integrated Visual Basic for Applications (VBA) programming language. No wonder it's today's computing tool of choice.

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 real-world 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, look-up 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 page-flip away.

book cover
Excel Scientific and Engineering Cookbook
By David M. Bourg
Publisher: O'Reilly
Pub Date: January 2006
Print ISBN-10: 0-596-00879-1
Print ISBN-13: 978-0-59-600879-6
Pages: 442

Table of Contents  | Index

      Recipe Who Should Read This Book
      Recipe What's in This Book
      Recipe Conventions in This Book
      Recipe Using Code Examples
      Recipe Safari Enabled
      Recipe We'd Like Your Feedback!
      Recipe Acknowledgments
        Chapter 1.  Using Excel
      Section 1.0.  Introduction
      Recipe 1.1.  Navigating the Interface
      Recipe 1.2.  Entering Data
      Recipe 1.3.  Setting Cell Data Types
      Recipe 1.4.  Selecting More Than a Single Cell
      Recipe 1.5.  Entering Formulas
      Recipe 1.6.  Exploring the R1C1 Cell Reference Style
      Recipe 1.7.  Referring to More Than a Single Cell
      Recipe 1.8.  Understanding Operator Precedence
      Recipe 1.9.  Using Exponents in Formulas
      Recipe 1.10.  Exploring Functions
      Recipe 1.11.  Formatting Your Spreadsheets
      Recipe 1.12.  Defining Custom Format Styles
      Recipe 1.13.  Leveraging Copy, Cut, Paste, and Paste Special
      Recipe 1.14.  Using Cell Names (Like Programming Variables)
      Recipe 1.15.  Validating Data
      Recipe 1.16.  Taking Advantage of Macros
      Recipe 1.17.  Adding Comments and Equation Notes
      Recipe 1.18.  Getting Help
        Chapter 2.  Getting Acquainted with Visual Basic for Applications
      Section 2.0.  Introduction
      Recipe 2.1.  Navigating the VBA Editor
      Recipe 2.2.  Writing Functions and Subroutines
      Recipe 2.3.  Working with Data Types
      Recipe 2.4.  Defining Variables
      Recipe 2.5.  Defining Constants
      Recipe 2.6.  Using Arrays
      Recipe 2.7.  Commenting Code
      Recipe 2.8.  Spanning Long Statements over Multiple Lines
      Recipe 2.9.  Using Conditional Statements
      Recipe 2.10.  Using Loops
      Recipe 2.11.  Debugging VBA Code
      Recipe 2.12.  Exploring VBA's Built-in Functions
      Recipe 2.13.  Exploring Excel Objects
      Recipe 2.14.  Creating Your Own Objects in VBA
      Recipe 2.15.  VBA Help
        Chapter 3.  Collecting and Cleaning Up Data
      Section 3.0.  Introduction
      Recipe 3.1.  Importing Data from Text Files
      Recipe 3.2.  Importing Data from Delimited Text Files
      Recipe 3.3.  Importing Data Using Drag-and-Drop
      Recipe 3.4.  Importing Data from Access Databases
      Recipe 3.5.  Importing Data from Web Pages
      Recipe 3.6.  Parsing Data
      Recipe 3.7.  Removing Weird Characters from Imported Text
      Recipe 3.8.  Converting Units
      Recipe 3.9.  Sorting Data
      Recipe 3.10.  Filtering Data
      Recipe 3.11.  Looking Up Values in Tables
      Recipe 3.12.  Retrieving Data from XML Files
        Chapter 4.  Charting
      Section 4.0.  Introduction
      Recipe 4.1.  Creating Simple Charts
      Recipe 4.2.  Exploring Chart Styles
      Recipe 4.3.  Formatting Charts
      Recipe 4.4.  Customizing Chart Axes
      Recipe 4.5.  Setting Log or Semilog Scales
      Recipe 4.6.  Using Multiple Axes
      Recipe 4.7.  Changing the Type of an Existing Chart
      Recipe 4.8.  Combining Chart Types
      Recipe 4.9.  Building 3D Surface Plots
      Recipe 4.10.  Preparing Contour Plots
      Recipe 4.11.  Annotating Charts
      Recipe 4.12.  Saving Custom Chart Types
      Recipe 4.13.  Copying Charts to Word
      Recipe 4-14.  Displaying Error Bars
        Chapter 5.  Statistical Analysis
      Section 5.0.  Introduction
      Recipe 5.1.  Computing Summary Statistics
      Recipe 5.2.  Plotting Frequency Distributions
      Recipe 5.3.  Calculating Confidence Intervals
      Recipe 5.4.  Correlating Data
      Recipe 5.5.  Ranking and Percentiles
      Recipe 5.6.  Performing Statistical Tests
      Recipe 5.7.  Conducting ANOVA
      Recipe 5.8.  Generating Random Numbers
      Recipe 5.9.  Sampling Data
        Chapter 6.  Time Series Analysis
      Section 6.0.  Introduction
      Recipe 6.1.  Plotting Time Series Data
      Recipe 6.2.  Adding Trendlines
      Recipe 6.3.  Computing Moving Averages
      Recipe 6.4.  Smoothing Data Using Weighted Averages
      Recipe 6.5.  Centering Data
      Recipe 6.6.  Detrending a Time Series
      Recipe 6.7.  Estimating Seasonal Indices
      Recipe 6.8.  Deseasonalization of a Time Series
      Recipe 6.9.  Forecasting
      Recipe 6.10.  Applying Discrete Fourier Transforms
        Chapter 7.  Mathematical Functions
      Section 7.0.  Introduction
      Recipe 7.1.  Using Summation Functions
      Recipe 7.2.  Delving into Division
      Recipe 7.3.  Mastering Multiplication
      Recipe 7.4.  Exploring Exponential and Logarithmic Functions
      Recipe 7.5.  Using Trigonometry Functions
      Recipe 7.6.  Seeing Signs
      Recipe 7.7.  Getting to the Root of Things
      Recipe 7.8.  Rounding and Truncating Numbers
      Recipe 7.9.  Converting Between Number Systems
      Recipe 7.10.  Manipulating Matrices
      Recipe 7.11.  Building Support for Vectors
      Recipe 7.12.  Using Spreadsheet Functions in VBA Code
      Recipe 7.13.  Dealing with Complex Numbers
        Chapter 8.  Curve Fitting and Regression
      Section 8.0.  Introduction
      Recipe 8.1.  Performing Linear Curve Fitting Using Excel Charts
      Recipe 8.2.  Constructing Your Own Linear Fit Using Spreadsheet Functions
      Recipe 8.3.  Using a Single Spreadsheet Function for Linear Curve Fitting
      Recipe 8.4.  Performing Multiple Linear Regression
      Recipe 8.5.  Generating Nonlinear Curve Fits Using Excel Charts
      Recipe 8.6.  Fitting Nonlinear Curves Using Solver
      Recipe 8.7.  Assessing Goodness of Fit
      Recipe 8.8.  Computing Confidence Intervals
        Chapter 9.  Solving Equations
      Section 9.0.  Introduction
      Recipe 9.1.  Finding Roots Graphically
      Recipe 9.2.  Solving Nonlinear Equations Iteratively
      Recipe 9.3.  Automating Tedious Problems with VBA
      Recipe 9.4.  Solving Linear Systems
      Recipe 9.5.  Tackling Nonlinear Systems of Equations
      Recipe 9.6.  Using Classical Methods for Solving Equations
        Chapter 10.  Numerical Integration and Differentiation
      Section 10.0.  Introduction
      Recipe 10.1.  Integrating a Definite Integral
      Recipe 10.2.  Implementing the Trapezoidal Rule in VBA
      Recipe 10.3.  Computing the Center of an Area Using Numerical Integration
      Recipe 10.4.  Calculating the Second Moment of an Area
      Recipe 10.5.  Dealing with Double Integrals
      Recipe 10.6.  Numerical Differentiation
        Chapter 11.  Solving Ordinary Differential Equations
      Section 11.0.  Introduction
      Recipe 11.1.  Solving First-Order Initial Value Problems
      Recipe 11.2.  Applying the Runge-Kutta Method to Second-Order Initial Value Problems
      Recipe 11.3.  Tackling Coupled Equations
      Recipe 11.4.  Shooting Boundary Value Problems
        Chapter 12.  Solving Partial Differential Equations
      Section 12.0.  Introduction
      Recipe 12.1.  Leveraging Excel to Directly Solve Finite Difference Equations
      Recipe 12.2.  Recruiting Solver to Iteratively Solve Finite Difference Equations
      Recipe 12.3.  Solving Initial Value Problems
      Recipe 12.4.  Using Excel to Help Solve Problems Formulated Using the Finite Element Method
        Chapter 13.  Performing Optimization Analyses in Excel
      Section 13.0.  Introduction
      Recipe 13.1.  Using Excel for Traditional Linear Programming
      Recipe 13.2.  Exploring Resource Allocation Optimization Problems
      Recipe 13.3.  Getting More Realistic Results with Integer Constraints
      Recipe 13.4.  Tackling Troublesome Problems
      Recipe 13.5.  Optimizing Engineering Design Problems
      Recipe 13.6.  Understanding Solver Reports
      Recipe 13.7.  Programming a Genetic Algorithm for Optimization
        Chapter 14.  Introduction to Financial Calculations
      Section 14.0.  Introduction
      Recipe 14.1.  Computing Present Value
      Recipe 14.2.  Calculating Future Value
      Recipe 14.3.  Figuring Out Required Rate of Return
      Recipe 14.4.  Doubling Your Money
      Recipe 14.5.  Determining Monthly Payments
      Recipe 14.6.  Considering Cash Flow Alternatives
      Recipe 14.7.  Achieving a Certain Future Value
      Recipe 14.8.  Assessing Net Present Worth
      Recipe 14.9.  Estimating Rate of Return
      Recipe 14.10.  Solving Inverse Problems
      Recipe 14.11.  Figuring a Break-Even Point
   About the Author