Engineering with Excel

ABOUT THIS BOOK vii <br> 1 • INTRODUCTION TO EXCEL 1 <br>1.1 Introduction 1 <br>1.2 What’s new in Excel 2010? 4 <br>1.2A Starting Excel 5 <br>1.3 A Little Windows® 6 <br>1.4 Excel Basics 16 <br>1.5 Organizing Your Worksheets 35 <br>1.6 Printing the Worksheet 36 <br>1.7 Saving and Opening Workbooks, Exiting Excel 41 <br> 2 • USING EXCEL’S RIBBON 53 <br>2.1 Navigating the Ribbon 54 <br>2.2 Using the Clipboard Group 54 <br>2.3 Using the Font Group 71 <br>2.4 Using the Alignment Group 79 <br>2.5 Formatting Numbers 85 <br>2.6 Using the Styles Group 93 <br>2.7 Inserting, Deleting and Formatting Rows and Columns 104 <br> 3 • GRAPHING WITH EXCEL 122 <br>3.1 Introduction 122 <br>3.2 Getting Ready to Graph 123 <br>3.3 Creating an XY Scatter Graph 124 <br>3.4 Editing an Existing Graph 128 <br>3.5 Printing the Graph 146 <br>3.6 Other Types of Graphs 146 <br>3.7 Graphing Web Data 156 <br>3.8 Importing Text Files 159 <br> 4 • EXCEL FUNCTIONS 175 <br>4.1 Introduction to Excel Functions 175 <br>4.2 Excel’s Built-in Functions 176 <br>4.3 Using the CONVERT Function to Convert Units 179 <br>4.4 Simple Math Functions 181 <br>4.5 Computing Sums 183 <br>4.6 Trigonometric Functions 187 <br>4.7 Advanced Math Functions 195 <br>4.8 Error Function 197 <br>4.9 Bessel Functions 198 <br>4.10 Working with Complex Numbers 198 <br>4.11 Working with Binary, Octal, and Hexadecimal Values 198 <br>4.12 Miscellaneous Functions 200 <br> 5 • MATRIX OPERATIONS IN EXCEL 221 <br>5.1 Introduction 221 <br>5.2 Matrices, Vectors, and Arrays 222 <br>5.3 How Excel Handles Matrix Math 223 <br>5.4 Basic Matrix Operations 223 <br>5.5 Solving Systems of Linear Equations 237 <br> 6 • LINEAR REGRESSION IN EXCEL 256 <br>6.1 Introduction 256 <br>6.2 Linear Regression Using Excel Functions 257 <br>6.3 Linear Regression Using Excel’s Trendlines 259 <br>6.4 Other Two-Coefficient Linear Regression Models 263 <br>6.5 Polynomial Regression 265 <br>6.6 Linear Regression Using Excel’s Regression Analysis Package 266 <br> 7 • EXCEL’S STATISTICS FUNCTIONS 291 <br>7.1 Overview 291 <br>7.2 Populations and Samples 291 <br>7.3 Standard Deviations and Variances 294 <br>7.4 Errors, Deviations, and Distributions 297 <br>7.5 Confidence Intervals 304 <br> 8 • EXCEL’S FINANCIAL FUNCTIONS 318 <br>8.1 Time, Money, and Cash Flows 318 <br>8.2 Interest Rates and Compounding 321 <br>8.3 Moving Amounts Through Time 325 <br>8.4 Net Present Value 335 <br>8.5 Internal Rate of Return 338 <br>8.6 Economic Alternatives: Making Decisions 340 <br>8.7 Depreciation of Assets 344 <br> 9 • ITERATIVE SOLUTIONS USING EXCEL 357 <br>9.1 Introduction 357 <br>9.2 Iterative Solutions 358 <br>9.3 Using a Plot to Search for Roots 359 <br>9.4 Simple “Guess and Check” Iteration 361 <br>9.5 Direct-Substitution Technique 363 <br>9.6 Using Goal Seek in Excel 368 <br>9.7 Introduction to Excel’s Solver 371 <br>9.8 Optimization Using the Solver 378 <br>9.9 Nonlinear Regression 383 <br>9.10 Linear Programming 394 <br> 10 • SHARING EXCEL INFORMATION WITH OTHER PROGRAMS 415 <br>10.1 Introduction to Sharing Excel Information with Other Programs 415 <br>10.2 Using Copy and Paste to Move Information 416 <br>10.3 Embedded and Linked Objects 428 <br>10.4 External Data Sources 438 <br> 11 • EXCEL PIVOT TABLES 451 <br>11.1 Introduction 451 <br>11.2 Preparing to Create a Pivot Table 452 <br>11.3 A Basic Pivot Table 453 <br>11.4 Sorting and Filtering a Pivot Table 455 <br>11.5 Pivoting the Pivot Table 459 <br>11.6 What Can Be Done with a Pivot Table? 461 <br>11.7 Pivot Charts 464 <br> 12 • MACROS AND USER-WRITTEN FUNCTIONS FOR EXCEL 470 <br>12.1 Introduction 470 <br>12.2 Macros and Viruses 471 <br>12.3 Recorded Macros 473 <br>12.4 Programmed Macros (VBA) 504 <br> 13 • USER-WRITTEN FUNCTIONS FOR EXCEL 524 <br>13.1 Introduction 524