# A Guide to Microsoft Excel 2013 for Scientists and Engineers

## Description

Completely updated guide for students, scientists and engineers who want to use Microsoft Excel 2013 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.

## Table of Contents

PrefaceConventions Used in this Book
Chapter 1: Welcome to Microsoft® Excel 2013Abstract
Exercise 1: Customizing the QAT
Exercise 2: Customizing the Ribbon Control
The Worksheet
Excel 2013 Specifications and Limits
Compatibility with Other Versions
Exercise 3: The Status Bar
Chapter 2: Basic OperationsAbstract
Exercise 1: Simple Arithmetic
Exercise 2: The Mathematical Operators
Exercise 3: Formatting (Displayed and Stored Values)
Exercise 4: Working with Fractions
Exercise 5: A Practical Worksheet
Copying Formulas: What Happens to References?
What’s in a Name?
Exercise 6: Another Practical Example
Exercise 7: The Evaluate Formula Tool
Special Symbols, Subscripts, and Superscripts
Mathematical Limitations of Excel
Play It Again, Sam
Chapter 3: Printing in ExcelAbstract
Exercise 1: Quick Print and Print Preview
The Print Preview Dialog
Exercise 2: The Page Layout Tab
Exercise 3: Header/Footers and Page Breaks
Exercise 4: Documentation and Printing Formulas
Chapter 4: Using FunctionsAbstract
Exercise 1: The AutoSum Tool
The Insert Function Command
Exercise 2: Computing a Weighted Average
Exercise 3: Entering Formulas by Typing
Exercise 4: Trigonometry Functions
Exercise 5: Exponential Functions
Exercise 6: The ROUND, ROUNDUP, and ROUNDDOWN Functions
Other Rounding Functions
Some Other Mathematical Functions
Array Formulas
Exercise 7: Matrix Functions
Volatility: Calculate Mode
Exercise 8: Solving Systems of Equations
Financial Functions
Exercise 9: Borrower Beware
Chapter 5: Conditional FunctionsAbstract
Logical Comparison Operators
Exercise 1: Boolean Functions
Exercise 2: Practical Example
The IF Function
Nested Ifs
IF Formulas with Boolean Functions
The IFERROR Function
Exercise 3: Resistors Revisited
Exercise 4: Quadratic Equation Solver
Exercise 5: Protecting a Worksheet
Table Lookup Functions
Exercise 6: A Simple Lookup
Exercise 7: A Two-Valued Lookup
Exercise 8: Conditional Arithmetic
Exercise 9: Array Formulas
Exercise 10: The SUMPRODUCT function
Exercise 11: Conditional Formatting
Chapter 6: Data MiningAbstract
Exercise 1: Importing TXT File
Exercise 2: Counting and Summing with Criteria
Exercise 3: Frequency Distribution
Exercise 4: Pivot Tables
Exercise 5: Sorting
Exercise 6: Filtering
Exercise 7: The Excel Table
Chapter 7: ChartsAbstract
Exercise 1: An XY Chart
Exercise 2: Plotting Functions
Changing the Position of Axes Crossing
Filtering a Chart with Many Data Series
Finding Roots
Exercise 3: Adding and Deleting Data Series
Exercise 4: XY Chart with Two Y-axes
Exercise 5: Chart with Control Lines
Exercise 6: Large Numbers and Log Scales
Exercise 7: Error Bars
Exercise 8: Plotting Parametric Equations
Other Chart Types
Exercise 9: Polar (Radar) Chart
Exercise 10: Surface Charts
Exercise 11: Combination Charts
Exercise 12: Bar Charts
Plotting Sine Curves
URLs for Chart Websites
Chapter 8: Regression AnalysisAbstract
Exercise 1: Trendline, SLOPE, and INTERCEPT
Exercise 2: Interpolation and FORECAST
Exercise 3: The LINEST Function
Exercise 4: Fixed Intercept
Exercise 5: Multilinear Regression
Exercise 6: A Polynomial Fit
Exercise 7: A Logarithmic Fit (LOGEST)
Exercise 8: The FORECAST, TREND, and GROWTH functions
Residuals
Exercise 9: Slope and Tangent
Exercise 10: The Analysis ToolPak
Chapter 9: VBA User-Defined FunctionsAbstract
Exercise 1: The Visual Basic Editor
Syntax of a Function
Exercise 2: A Simple Function
Naming Functions and Variables
Worksheet and VBA Functions
Exercise 3: When Things Go Wrong
Programming Structures
Exercise 4: The IF Structure
Exercise 5: Boolean Operators
The Select…Case Structure
Exercise 6: Select Example
The For…Next Structure
The Excel Object Model: An Introduction
Exercise 7: For Each…Next
Exercise 8: The Do…Loop Structure
Variables and Data Types
Input-output of Arrays
Exercise 9: An Array Function
Using Functions from Other Workbooks
Chapter 10: VBA SubroutinesAbstract
Exercise 1: Recording a Macro
Subroutines That Compute
Notes on the VB Editor
Exercise 2: A Computing Macro
Name That Variable
Exercise 3: Bolt Hole Positions
Exercise 4: Finding Roots by Bisection
Exercise 5: Using Arrays
Exercise 6: Adding a Control
Exercise 7: User Forms
Chapter 11: Modeling IAbstract
Exercise 1: Population Model
Exercise 2: Vapor Pressure of Ammonia
Exercise 3: Stress Analysis
Exercise 4: Circuit Analysis
Exercise 5: Ladder Down the Mine
Exercise 6: Adding Waves
Exercise 7: Centroid of a Polygon
Exercise 8: Finding Roots by Iteration
Chapter 12: Using SolverAbstract
Exercise 1: Goal Seek
Exercise 2: Solver as a Root Finder
Solving Equations with Constraints
Exercise 3: Finding Multiple Roots
Exercise 4: Systems of Nonlinear Equations
Exercise 5: Curve Fitting with Solver
Exercise 6: Gaussian Curve Fit
Exercise 7: Solver Versus Linearization
Exercise 8: A Minimization Problem
Exercise 9: An Optimization Problem
Chapter 13: Numerical IntegrationAbstract
Exercise 1: The Trapezoid Rule
Exercise 2: Simpson’s ⅓ Rule
Exercise 3: Adding Flexibility
Exercise 4: Going Modular
Exercise 5: Tabular Data
Exercise 6: Gaussian Integration
Exercise 7: Monte Carlo Techniques
Chapter 14: Differential EquationsAbstract
Exercise 1: Euler’s Method
Exercise 2: The Runge-Kutta Methods
Exercise 3: Solving with a User-Defined Function
Simultaneous and Second-Order Differential Equations
Exercise 4: Solving a Second-Order Equation
Exercise 5: The Simple Pendulum
Chapter 15: Modeling IIAbstract
Exercise 1: The Four-Bar Crank
Exercise 2: Temperature Profile Using Matrix Algebra
Exercise 3: Temperature Profile Using Solver
Exercise 4: Emptying the Tank
Exercise 5: An Improved Tank Emptying Model
Chapter 16: Statistics for ExperimentersAbstract
Exercise 1: Descriptive Statistics
Exercise 2: Frequency Distribution
Exercise 3: Confidence Limits
Exercise 4: The Experimental and Expected Mean
Exercise 5: Pooled Standard Deviation
Exercise 6: Comparing Paired Arrays
Exercise 7: Comparing Repeated Measurements
Exercise 8: The Calibration Curve Revisited
Exercise 9: More on the Calibration Curve
Appendix: AnswersChapter 2
Chapter 4
Chapter 5
Chapter 7
Chapter 8
Chapter 9
Chapter 10
Chapter 11
Chapter 12
Chapter 13
Chapter 14
Chapter 16
Index