Guide to Microsoft Excel 2007 for Scientists and Engineers (eBook)

Front Cover 1
A Guide to Microsoft® Excel 2007 for Scientists and Engineers 2
Copyright Page 3
Contents 4
Preface 10
Chapter 1 Welcome to Microsoft Excel® 2007 12
The Excel Window 12
Exercise 1: The Ribbon 16
Exercise 2: Quick Access Toolbar 17
Exercise 3: Working with Shortcuts 18
The Worksheet 18
Excel 2007 Specifications 20
Problems 22
Chapter 2 Basic Operations 23
Exercise 1: Simple Arithmetic 23
Exercise 2: The Arithmetic Operators 26
Exercise 3: Formatting (Displayed and Stored Values) 29
Exercise 4: Working with Fractions 30
Exercise 5: A Practical Worksheet 32
Copying Formulas: What Happens to References? 33
What’s in a Name? 35
Exercise 6: Another Practical Example 36
Special Symbols, Subscripts, and Superscripts 40
Mathematical Limitations of Excel 42
Play It Again, Sam 44
Problems 44
Chapter 3 Printing in Excel 49
Exercise 1: Quick Print and Print Preview 49
Exercise 2: Print Area 51
The Print Dialog 51
Exercise 3: Some Printing Options 52
Chapter 4 Using Functions 55
Exercise 1: AutoSum Tool 57
The Insert Function Command 59
Exercise 2: Computing a Weighted Average 59
Exercise 3: Entering Formulas by Typing 62
Exercise 4: Trigonometry Functions 63
Exercise 5: Exponential Functions 65
Exercise 6: Rounding Functions 65
Note on Rounding 66
Significant Numbers 67
Some Other Mathematical Functions 67
Array Formulas 68
Exercise 7: The Matrix Functions 68
Volatility: Calculate Mode 69
Exercise 8: Solving Systems of Equations 70
Exercise 9: Sum of Diagonal 72
Financial Functions 73
Problems 75
Chapter 5 Decision Functions 80
Logical Comparison Operators 80
Exercise 1: Boolean Functions 81
Exercise 2: Practical Example 81
The IF Function 82
Exercise 3: Resistors Revisited 84
Exercise 4: Quadratic Equation Solver 85
Exercise 5: Protecting a Worksheet 87
Table Lookup Functions 89
Exercise 6: A Simple Lookup 90
Exercise 7: A Two-Valued Lookup 91
Exercise 8: Conditional Summing 92
Exercise 9: Array Formulas 94
Exercise 10: Conditional Formatting 96
Exercise 11: SUMPRODUCT 97
Problems 99
Chapter 6 Data Mining 102
Exercise 1: Importing a TXT file 102
Exercise 2: Counting and Summing with Criteria 103
Exercise 3: Frequency Distribution 104
Exercise 4: Pivot Tables 105
Exercise 5: Sorting 106
Exercise 6: Filtering 107
Exercise 7: The Excel Table 108
Problems 109
Chapter 7 Charts 112
Types of Charts 112
Line and XY Chart 112
Comments about Charts 113
Chart Terminology 114
Exercise 1: An XY Chart 114
Exercise 2: Smooth Lines 116
Formatting a Chart 117
Exercise 3: Formatting the Data Series 118
Exercise 4: Formatting an Axis 119
Exercise 5: Plotting a Function 120
Exercise 6: More Formatting 121
Finding Roots 121
Exercise 7: A Flexible Domain 121
Exercise 8: Changing Axis Position 123
Exercise 9: XY Chart with Two Y-axes 123
Exercise 10: Control Chart 125
Exercise 11: Too Much Data 127
Exercise 12: Large Numbers and Log Scale 128
Exercise 13: Error Bars 129
Other Chart Types 130
Exercise 14: Surface Chart 130
Exercise 15: Combination Chart 131
Exercise 16: A Bar Chart 132
Exercise 17: A Parametric Chart 133
Exercise 18: Polar Chart 134
Dynamic Charts 135
Printing a Chart 135
URLs for Chart Websites 135
Problems 136
Chapter 8 Regression Analysis 141
Least-Squares Fitting 141
Exercise 1: Trendline, SLOPE, and INTERCEPT 141
Exercise 2: Interpolation and FORECAST 144
Exercise 3: The LINEST Function 145
Exercise 4: Fixed Intercept 146
Exercise 5: A Polynomial Fit 147
Exercise 6: A Logarithmic Fit (LOGEST) 148
The TREND and GROWTH Functions 150
Residuals 150
Exercise 7: Slope and Tangent 151
Exercise 8: The Analysis Toolpak 153
Problems 155
Chapter 9 VBA User-defined Functions 159
Security Note 159
Exercise 1: The Visual Basic Editor 160
Syntax of a Function 161
Exercise 2: A Simple Function 162
Naming Functions and Variables 164
Worksheet and VBA Functions 165
Exercise 3: When Things Go Wrong 166
Programming Structures 167
Exercise 4: The IF Structure 167
Exercise 5: Boolean Operators 167
The SELECT Structure 169
Exercise 6: Select Example 170
The FOR..NEXT Structure 171
Exercise 7: Example Using FOR ... NEXT 171
The Excel Object Model: An Introduction 172
Exercise 8: FOR EACH—Resistors Revisited 173
Exercise 9: The DO ... LOOP Structure 174
Variables and Data Types 177
Input-Output of Arrays 177
Exercise 10: An Array Function 178
Using Functions from Other Workbooks 179
Problems 180
Chapter 10 VBA Subroutines 184
Exercise 1: Recording a Macro 184
Computing Subroutines 186
Notes on the VB Editor 187
Exercise 2: A Computing Macro 188
Public or Private? 190
Name That Variable 190
Exercise 3: Bolt Hole Positions 190
Exercise 4: Finding Roots by Bisection 192
Exercise 5: Using Arrays 196
Exercise 6: Adding a Control 198
Exercise 7: User Forms 199
Problems 202
Chapter 11 Modeling I 205
Exercise 1: Population Model 205
Exercise 2: Vapor Pressure of Ammonia 207
Exercise 3: Stress Analysis 209
Exercise 4: Circuit Analysis 211
Exercise 5: Ladder Down the Mine 212
Exercise 6: Adding Waves 214
Exercise 7: Centroid of a Polygon 215
Exercise 8: Finding Roots by Iteration 218
Problems 219
Chapter 12 Using Solver 222
Exercise 1: Goal Seek 222
Exercise 2: Solver as Root Finder 224
Solving Equations with Constraints 225
Exercise 3: Finding Multiple Roots 226
Exercise 4: Saving Solver Models 227
Exercise 5: Systems of Nonlinear Equations 228
Curve Fitting with Solver 229
Exercise 6: Gaussian Curve Fit 231
Exercise 7: A Minimization Problem 232
Exercise 8: An Optimization Problem 233
TK SolverTM 235
Problems 236
Chapter 13 Numerical Integration 239
Exercise 1: The Trapezoid Rule 241
Exercise 2: Simpson’s 1/3 Rule 243
Exercise 3: Adding Flexibility 245
Exercise 4: Going Modular 246
Exercise 5: Tabular Data 248
Exercise 6: Gaussian Integration 250
Exercise 7: Monte Carlo Techniques 253
Problems 255
Chapter 14 Differential Equations 258
Exercise 1: Euler’s Method 259
Exercise 2: The Runge–Kutta Method 262
Exercise 3: Solving with a User-Defined Function 263
Simultaneous and Second-Order Differential Equations 265
Exercise 4: Solving a Second-Order Equation 266
Exercise 5: The Simple Pendulum 267
Problems 269
Chapter 15 Modeling II 272
Exercise 1: The Four-Bar Crank 272
Exercise 2: Temperature Profile Using Matrix Algebra 275
Exercise 3: Temperature Profile Using Solver 278
Exercise 4: Emptying the Tank 280
Exercise 5: An Improved Tank Emptying Model 282
Problems 285
Chapter 16 Statistics for Experimenters 286
Exercise 1: Descriptive Statistics 286
Exercise 2: Frequency Distribution 288
Exercise 3: Confidence Limits 290
Exercise 4: The Experimental and Expected Mean 292
Exercise 5: Pooled Standard Deviation 295
Exercise 6: Comparing Paired Arrays 296
Exercise 7: Comparing Repeated Measurements 298
Exercise 8: The Calibration Curve Revisited 301
Exercise 9: More on the Calibration Curve 303
Problems 306
Chapter 17 Report Writing 309
Documentation 309
Picture of Worksheet 309
Display Formulas 310
Exercise 1: Creating an Equation 310
Screen Captures 313
MathLookTM 313
Copy and Paste or OLE? 315
Exercise 2: Copy and Paste 315
Exercise 3: Object Embedding 317
Microsoft Visio® 318
Answers 319
Index 332