# A Guide to Microsoft Excel 2013 for Scientists and Engineers

## 1st Edition

**Author:**Bernard Liengme

**Paperback ISBN:**9780128028179

**eBook ISBN:**9780128028162

**Imprint:**Academic Press

**Published Date:**13th March 2015

**Page Count:**382

## 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.

## 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 2013.
- Features of Excel 2013 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.

New to this edition:

- The Backstage is introduced (a new Office 2013 feature); all the ‘external’ operations like Save, Print etc. are now in one place
- The chapter on charting is totally revised and updated – Excel 2013 differs greatly from earlier versions
- Includes many new end-of-chapter problems
- Most chapters have been edited to improve readability

## Readership

Undergraduate science and engineering students; professional scientists and engineers.

## Table of Contents

- Preface
- Conventions Used in this Book

- Chapter 1: Welcome to Microsoft® Excel 2013
- Abstract
- 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 Operations
- Abstract
- 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 Excel
- Abstract
- 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 Functions
- Abstract
- 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 Functions
- Abstract
- 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 Mining
- Abstract
- 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: Charts
- Abstract
- 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 Analysis
- Abstract
- 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 Functions
- Abstract
- 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 Subroutines
- Abstract
- 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 I
- Abstract
- 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 Solver
- Abstract
- 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 Integration
- Abstract
- 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 Equations
- Abstract
- 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 II
- Abstract
- 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 Experimenters
- Abstract
- 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: Answers
- Chapter 2
- Chapter 4
- Chapter 5
- Chapter 7
- Chapter 8
- Chapter 9
- Chapter 10
- Chapter 11
- Chapter 12
- Chapter 13
- Chapter 14
- Chapter 16

- Index

## Details

- No. of pages:
- 382

- Language:
- English

- Copyright:
- © Academic Press 2016

- Published:
- 13th March 2015

- Imprint:
- Academic Press

- Paperback ISBN:
- 9780128028179

- eBook ISBN:
- 9780128028162

## About the Author

### Bernard Liengme

Dr. Bernard Liengme attended Imperial College in London and received a BSc & Ph.D. in Chemistry. He also received post-docs at Carnegie-Mellon University in Pittsburgh and the University of British Columbia. He has conducted extensive research in surface chemistry and Mossbauer Effect. He has been at St Francis Xavier University in Canada since 1968 as professor, Associate Dean, and Registrar as well as teaching chemistry and computer science. He is the author of four previous versions of “A Guide to Microsoft Excel for Scientists and Engineers,” most recently the Excel 2013 version.

### Affiliations and Expertise

St Francis Xavier University, Antigonish, NS, Canada