Spreadsheet Problem Solving and Programming for Engineers and Scientists

✍ Scribed by David E. Clough; Steven C. Chapra

• Publisher: CRC Press
• Year: 2023
• Tongue: English
• Leaves: 452
• Category: Library

✦ Synopsis

Spreadsheet Problem Solving and Programming for Engineers and Scientists provides a comprehensive resource essential to a full understanding of modern spreadsheet skills needed for engineering and scientific computations. Beginning with the basics of spreadsheets and programming, the book builds on the authors’ decades of experience teaching spreadsheets and programming to both university students and professional engineers. Following on from this, it covers engineering economics, key numerical methods, and applied statistics. Finally, the book details the Visual Basic for Applications (VBA) programming system that accompanies Excel. With each chapter including examples and a set of exercises, the book is an ideal companion for all engineering courses and also for self-study. Based on the latest version of Excel (Microsoft Excel for Microsoft 365, Version 2201), it is also compatible with earlier versions of Excel dating back to Version 2013. Including numerous case studies, the book will be of interest to students and professionals working in all areas of engineering.

✦ Table of Contents

Cover
Half Title
Title Page
Copyright Page
Dedication
Contents
List of Examples
Preface
About the Authors
Chapter 1: Spreadsheet Basics
1.1. A Little History
1.2. Navigating the Spreadsheet
1.2.1. Cell Jumps and Selections
1.2.2. Copying and Moving Cells
1.3. How Information is Entered, Stored, and Edited in Cells
1.3.1. How Information is Entered in Cells
1.3.2. How Information is Stored in Memory
1.3.3. How Information is Edited in Cells
1.4. Entering Simple Formulas and Using Built-in Functions
1.5. Formatting the Spreadsheet
Problems
Notes
Chapter 2: Charts and Graphs
2.1. Engineering and Scientific Graphs
2.1.1. Plotting Smaller Data Sets
2.1.2. Plotting Large Data Sets
2.2. Plotting More Than One Series
2.3. Creating Plots of Two-dimensional Data
2.4. Bar and Pie Charts
2.5. Utilizing Chart Templates
Problems
Notes
Chapter 3: Engineering and Scientific Formulas
3.1. Cell Addressing
3.1.1. Cell Addressing in Formulas
3.2. Naming Cells
3.3. Creating Formulas Using Excel's Operators
3.4. Use of Built-in Functions in Engineering and Scientific Formulas
3.4.1. Trigonometric Functions
3.4.2. Hyperbolic Functions
3.4.3. Logarithmic and Exponential Functions
3.4.4. General Functions
3.5. Array Formulas
Problems
Notes
Chapter 4: Table-based Calculations
4.1. Sorting Tables
4.2. Determining Ranks of Table Entries
4.3. Converting Tables to Excel Lists
4.4. Excel Tables
4.5. Lookup Functions
4.5.1. The LOOKUP Function
4.5.2. The VLOOKUP and HLOOKUP Functions
4.5.3. The MATCH and INDEX Functions
4.5.4. XLOOKUP and XMATCH
4.5.5. Property Table Interpolation
4.6. Pivot Tables and Pivot Charts
Problems
Notes
Chapter 5: Case Studies and Targeting
5.1. Data Tables and the Table Function - One-way Case Studies
5.2. Data Tables for Two-way Case Studies
5.3. Manual Targeting Calculations and Goal Seek
5.4. The Solver
Problems
Notes
Chapter 6: Financial Calculations
6.1. Basic Economic Functions
6.2. Financial Flow in Manufacturing Operations
6.3. Constructing Cash Flow Tables
6.4. Profitability Measures
Problems
Notes
Chapter 7: Numerical Methods
7.1. Single Nonlinear Algebraic Equations
7.1.1. Bracketing Methods - Bisection
7.1.2. Open Methods - Newton-Raphson
7.2. Matrix Operations
7.3. Solving Sets of Linear Algebraic Equations
7.4. Solving Sets of Nonlinear Algebraic Equations
7.5. Solving Differential Equations
7.5.1. Solutions by Quadrature
7.5.2. Solving Initial-Value Ordinary Differential Equations
7.6. Implicit Nonlinear Relationships Yielding Circular Calculations
Problems
Notes
Chapter 8 Applied Statistics
8.1. Descriptive Statistics
8.2. Statistical Distributions and Random Number Generation
8.2.1. Statistical Distributions
8.2.2. Random Number Generation
8.3. Making Claims Using Statistical Hypothesis Tests
8.3.1. Hypothesis Tests Comparing to a Standard
8.3.2. Hypothesis Tests Comparing Two Samples
8.4. Using Regression to Fit Models to Data
8.4.1. Linear Regression
8.4.2. Nonlinear Regression
8.4.3. Fitting Models to Data Using Trendline
Problems
Notes
Chapter 9: Introduction to VBA and Macros
9.1. VBA and its Environment: The Visual Basic Editor
9.2. Recording Macros
9.3. Debugging VBA Code
9.4. Using the Macro Recorder as a Code Detective
Problems
Notes
Chapter 10: User-defined Functions
10.1. Automating Engineering Formulas as User-defined Functions
10.2. User-defined Functions with Cell Range Arguments
10.3. Creating User-defined Array Functions
10.4. Packaging Functions in an Add-in Problems
Problems
Notes
Chapter 11: VBA Programming
11.1. VBA Data Types
11.2. Arrays in VBA
11.3. Objects, Properties, Methods, and Events
11.4. Variable Scope
11.5. VBA Programming Structures
11.5.1. Selection Structures
11.5.2. Repetition Structures
11.6. VBA Programming Applications
Problems
Notes
Chapter 12: User Interfaces
12.1. On-sheet Buttons
12.2. Message Boxes
12.3. Input Boxes
12.4. Event Handlers
12.5. Userforms
12.6. User Interface Case Study
12.7. Linking Excel to Other Software Applications
12.7.1 MATLAB®
12.7.2. Aspen Technology's Hysys Process Simulation Software
Problems
Notes
Appendix A: Matrix Algebra Review
Appendix B: Shortcut Keys and Key Combinations
References
Index
Index of Excel-VBA Terminology