You are previewing A Guide to Microsoft Excel 2007 for Scientists and Engineers.
O'Reilly logo
A Guide to Microsoft Excel 2007 for Scientists and Engineers

Book Description

Completely updated guide for scientists, engineers and students who want to use Microsoft Excel 2007 to its full potential.

Electronic spreadsheet analysis has become part of the everyday work of researchers in all areas of engineering and science. Microsoft Excel, as the industry standard spreadsheet, has a range of scientific functions that can be utilized for the modeling, analysis and presentation of quantitative data. This text provides a straightforward guide to using these functions of Microsoft Excel, guiding the reader from basic principles through to more complicated areas such as formulae, charts, curve-fitting, equation solving, integration, macros, statistical functions, and presenting quantitative data.


Key Features:

* Content written specifically for the requirements of science and engineering students and professionals working with Microsoft Excel, brought fully up to date with the new Microsoft Office release of Excel 2007.
* Features of Excel 2007 are illustrated through a wide variety of examples based in technical contexts, demonstrating the use of the program for analysis and presentation of experimental results.
* Updated with new examples, problem sets, and applications. New website with data sets, downloadable spreadsheets and other useful resources.

Table of Contents

  1. Cover image
  2. Title page
  3. Table of Contents
  4. Copyright
  5. Preface
    1. Conventions Used in this Book
  6. Chapter 1. Welcome to Microsoft Excel® 2007
    1. Publisher Summary
    2. The Excel Window
    3. Exercise 1: The Ribbon
    4. Exercise 2: Quick Access Toolbar
    5. Exercise 3: Working with Shortcuts
    6. The Worksheet
    7. Excel 2007 Specifications
    8. Problems
  7. Chapter 2. Basic Operations
    1. Publisher Summary
    2. Exercise 1: Simple Arithmetic
    3. Exercise 2: The Arithmetic Operators
    4. Exercise 3: Formatting (Displayed and Stored Values)
    5. Exercise 4: Working with Fractions
    6. Exercise 5: A Practical Worksheet
    7. Copying Formulas: What Happens to References?
    8. What’s in a Name?
    9. Exercise 6: Another Practical Example
    10. Special Symbols, Subscripts and Superscripts
    11. Mathematical Limitations of Excel
    12. Play It Again, Sam
    13. Problems
  8. Chapter 3. Printing in Excel
    1. Publisher Summary
    2. Exercise 1: Quick Print and Print Preview
    3. Exercise 2: Print Area
    4. The Print Dialog
    5. Exercise 3: Some Printing Options
  9. Chapter 4. Using Functions
    1. Publisher Summary
    2. Exercise 1: AutoSum Tool
    3. The Insert Function Command
    4. Exercise 2: Computing a Weighted Average
    5. Exercise 3: Entering Formulas by Typing
    6. Exercise 4: Trigonometry Functions
    7. Exercise 5: Exponential Functions
    8. Exercise 6: Rounding Functions
    9. Note on Rounding
    10. Significant Numbers
    11. Some Other Mathematical Functions
    12. Array Formulas
    13. Exercise 7: The Matrix Functions
    14. Volatility: Calculate Mode
    15. Exercise 8: Solving Systems of Equations
    16. Exercise 9: Sum of Diagonal
    17. Financial Functions
    18. Problems
  10. Chapter 5. Decision Functions
    1. Publisher Summary
    2. Logical Comparison Operators
    3. Exercise 1: Boolean Functions
    4. Exercise 2: Practical Example
    5. The IF Function
    6. Exercise 3: Resistors Revisited
    7. Exercise 4: Quadratic Equation Solver
    8. Exercise 5: Protecting a Worksheet
    9. Table Lookup Functions
    10. Exercise 6: A Simple Lookup
    11. Exercise 7: A Two-Valued Lookup
    12. Exercise 8: Conditional Summing
    13. Exercise 9: Array Formulas
    14. Exercise 10: Conditional Formatting
    15. Exercise 11: SUMPRODUCT
    16. Problems
  11. Chapter 6. Data Mining
    1. Publisher Summary
    2. Exercise 1: Importing a TXT file
    3. Exercise 2: Counting and Summing with Criteria
    4. Exercise 3: Frequency Distribution
    5. Exercise 4: Pivot Tables
    6. Exercise 5: Sorting
    7. Exercise 6: Filtering
    8. Exercise 7: The Excel Table
    9. Problems
  12. Chapter 7. Charts
    1. Publisher Summary
    2. Types of Charts
    3. Line and XY Chart
    4. Comments about Charts
    5. Chart Terminology
    6. Exercise 1: An XY Chart
    7. Exercise 2: Smooth Lines
    8. Formatting a Chart
    9. Exercise 3: Formatting the Data Series
    10. Exercise 4: Formatting an Axis
    11. Exercise 5: Plotting a Function
    12. Exercise 6: More Formatting
    13. Finding Roots
    14. Exercise 7: A Flexible Domain
    15. Exercise 8: Changing Axis Position
    16. Exercise 9: XY Chart with Two Y-axes
    17. Exercise 10: Control Chart
    18. Exercise 11: Too Much Data
    19. Exercise 12: Large Numbers and Log Scale
    20. Exercise 13: Error Bars
    21. Other Chart Types
    22. Exercise 14: Surface Chart
    23. Exercise 15: Combination Chart
    24. Exercise 16: A Bar Chart
    25. Exercise 17: A Parametric Chart
    26. Exercise 18: Polar Chart
    27. Dynamic Charts
    28. Printing a Chart
    29. URLs for Chart Websites
    30. Problems
  13. Chapter 8. Regression Analysis
    1. Publisher Summary
    2. Least-Squares Fitting
    3. Exercise 1: Trendline, SLOPE, and INTERCEPT
    4. Exercise 2: Interpolation and FORECAST
    5. Exercise 3: The LINEST Function
    6. Exercise 4: Fixed Intercept
    7. Exercise 5: A Polynomial Fit
    8. Exercise 6: A Logarithmic Fit (LOGEST)
    9. The TREND and GROWTH Functions
    10. Residuals
    11. Exercise 7: Slope and Tangent
    12. Exercise 8: The Analysis Toolpak
    13. Problems
  14. Chapter 9. VBA User-Defined Functions
    1. Publisher Summary
    2. Security Note
    3. Exercise 1: The Visual Basic Editor
    4. Syntax of a Function
    5. Exercise 2: A Simple Function
    6. Naming Functions and Variables
    7. Worksheet and VBA Functions
    8. Exercise 3: When Things Go Wrong
    9. Programming Structures
    10. Exercise 4: The IF Structure
    11. Exercise 5: Boolean Operators
    12. The SELECT Structure
    13. Exercise 6: Select Example
    14. The FOR..NEXT Structure
    15. Exercise 7: Example Using FOR…NEXT
    16. The Excel Object Model: An Introduction
    17. Exercise 8: FOR EACH—Resistors Revisited
    18. Exercise 9: The DO …LOOP Structure
    19. Variables and Data Types
    20. Input-Output of Arrays
    21. Exercise 10: An Array Function
    22. Using Functions from Other Workbooks
    23. Problems
  15. Chapter 10. VBA Subroutines
    1. Publisher Summary
    2. Exercise 1: Recording a Macro
    3. Computing Subroutines
    4. Notes on the VB Editor
    5. Exercise 2: A Computing Macro
    6. Public or Private?
    7. Name That Variable
    8. Exercise 3: Bolt Hole Positions
    9. Exercise 4: Finding Roots by Bisection
    10. Exercise 5: Using Arrays
    11. Adding a Control
    12. Exercise 7: User Forms
    13. Problems
  16. Chapter 11. Modeling I
    1. Publisher Summary
    2. Exercise 1: Population Model
    3. Exercise 2: Vapor Pressure of Ammonia
    4. Exercise 3: Stress Analysis
    5. Exercise 4: Circuit Analysis
    6. Exercise 5: Ladder Down the Mine
    7. Exercise 6: Adding Waves
    8. Exercise 7: Centroid of a Polygon
    9. Exercise 8: Finding Roots by Iteration
    10. Problems
  17. Chapter 12. Using Solver
    1. Publisher Summary
    2. Exercise 1: Goal Seek
    3. Exercise 2: Solver as Root Finder
    4. Solving Equations with Constraints
    5. Exercise 3: Finding Multiple Roots
    6. Exercise 4: Saving Solver Models
    7. Exercise 5: Systems of Nonlinear Equations
    8. Curve Fitting with Solver
    9. Exercise 6: Gaussian Curve Fit
    10. Exercise 7: A Minimization Problem
    11. Exercise 8: An Optimization Problem
    12. Tk Solver™
    13. Problems
  18. Chapter 13. Numerical Integration
    1. Publisher Summary
    2. Numerical Integration
    3. Exercise 1: The Trapezoid Rule
    4. Exercise 2: Simpson's ⅓ Rule
    5. Exercise 3: Adding Flexibility
    6. Exercise 4: Going Modular
    7. Exercise 5: Tabular Data
    8. Exercise 6: Gaussian Integration
    9. Exercise 7: Monte Carlo Techniques
    10. Problems
  19. Chapter 14. Differential Equations
    1. Publisher Summary
    2. Exercise 1: Euler's Method
    3. Exercise 2: The Runge–Kutta Method
    4. Exercise 3: Solving with a User-Defined Function
    5. Simultaneous and Second-Order Differential Equations
    6. Exercise 4: Solving a Second-Order Equation
    7. Exercise 5: The Simple Pendulum
    8. Problems
  20. Chapter 15. Modeling II
    1. Publisher Summary
    2. Exercise 1: The Four-Bar Crank
    3. Exercise 2: Temperature Profile Using Matrix Algebra
    4. Exercise 3: Temperature Profile using Solver
    5. Exercise 4: Emptying the Tank
    6. Exercise 5: An Improved Tank Emptying Model
    7. Problems
  21. Chapter 16. Statistics for Experimenters
    1. Publisher Summary
    2. Exercise 1: Descriptive Statistics
    3. Exercise 2: Frequency Distribution
    4. Exercise 3: Confidence Limits
    5. Exercise 4: The Experimental and Expected Mean
    6. Exercise 5: Pooled Standard Deviation
    7. Exercise 6: Comparing Paired Arrays
    8. Exercise 7: Comparing Repeated Measurements
    9. Exercise 8: The Calibration Curve Revisited
    10. Exercise 9: More on the Calibration Curve
    11. Problems
  22. Chapter 17. Report Writing
    1. Publisher Summary
    2. Documentation
    3. Picture of Worksheet
    4. Display Formulas
    5. Exercise 1: Creating an Equation
    6. Screen Captures
    7. MathLook™
    8. Copy and Paste or OLE?
    9. Exercise 2: Copy and Paste
    10. Exercise 3: Object Embedding
    11. Microsoft Visio®
  23. Answers
    1. Chapter 2
    2. Chapter 4
    3. Chapter 5
    4. Chapter 7
    5. Chapter 8
    6. Chapter 9
    7. Chapter 10
    8. Chapter 11
    9. Chapter 12
    10. Chapter 13
    11. Chapter 14
    12. Chapter 16
  24. Index