You are previewing Excel Scientific and Engineering Cookbook.
O'Reilly logo
Excel Scientific and Engineering Cookbook

Book Description

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.

Table of Contents

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