Excel Scientific and Engineering Cookbook

Excel Scientific and Engineering Cookbook

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