Formulas and Functions with Microsoft® Office Excel 2007......Page 1
Contents......Page 5
Introduction......Page 18
This Book’s Special Features......Page 19
I: MASTERING EXCEL RANGES AND FORMULAS......Page 22
Advanced Range-Selection Techniques......Page 24
Data Entry in a Range......Page 32
Using the Fill Handle......Page 33
Creating a Series......Page 36
Advanced Range Copying......Page 37
Clearing a Range......Page 40
Applying Conditional Formatting to a Range......Page 41
2 Using Range Names......Page 54
Defining a Range Name......Page 55
Working with Range Names......Page 62
Understanding Formula Basics......Page 72
Understanding Operator Precedence......Page 76
Controlling Worksheet Calculation......Page 79
Copying and Moving Formulas......Page 81
Converting a Formula to a Value......Page 84
Working with Range Names in Formulas......Page 85
Working with Links in Formulas......Page 89
Formatting Numbers, Dates, and Times......Page 92
Working with Arrays......Page 106
Using Iteration and Circular References......Page 112
Consolidating Multisheet Data......Page 114
Applying Data-Validation Rules to Cells......Page 119
Using Dialog Box Controls on a Worksheet......Page 122
5 Troubleshooting Formulas......Page 130
Understanding Excel’s Error Values......Page 131
Fixing Other Formula Errors......Page 135
Handling Formula Errors with IFERROR()......Page 138
Using the Formula Error Checker......Page 139
Auditing a Worksheet......Page 143
II: HARNESSING THE POWER OF FUNCTIONS......Page 148
6 Understanding Functions......Page 150
The Structure of a Function......Page 151
Typing a Function into a Formula......Page 153
Using the Insert Function Feature......Page 155
Loading the Analysis ToolPak......Page 157
7 Working with Text Functions......Page 160
Excel’s Text Functions......Page 161
Working with Characters and Codes......Page 162
Converting Text......Page 166
Formatting Text......Page 167
Removing Unwanted Characters from a String......Page 169
Extracting a Substring......Page 172
Searching for Substrings......Page 175
Substituting One Substring for Another......Page 179
Generating Account Numbers, Part 2......Page 182
Adding Intelligence with Logical Functions......Page 184
Building an Accounts Receivable Aging Worksheet......Page 199
Getting Data with Information Functions......Page 201
9 Working with Lookup Functions......Page 212
Understanding Lookup Tables......Page 213
The CHOOSE() Function......Page 214
Looking Up Values in Tables......Page 217
How Excel Deals with Dates and Times......Page 230
Using Excel’s Date Functions......Page 233
Using Excel’s Time Functions......Page 250
Building an Employee Time Sheet......Page 255
11 Working with Math Functions......Page 260
Understanding Excel’s Rounding Functions......Page 264
Summing Values......Page 270
The MOD() Function......Page 272
Generating Random Numbers......Page 276
12 Working with Statistical Functions......Page 280
Understanding Descriptive Statistics......Page 282
Counting Items with the COUNT() Function......Page 283
Calculating Averages......Page 284
Calculating Extreme Values......Page 286
Calculating Measures of Variation......Page 289
Working with Frequency Distributions......Page 292
Using the Analysis ToolPak Statistical Tools......Page 297
III: BUILDING BUSINESS MODELS......Page 312
13 Analyzing Data with Tables......Page 314
Converting a Range to a Table......Page 316
Basic Table Operations......Page 317
Sorting a Table......Page 318
Filtering Table Data......Page 323
Referencing Tables in Formulas......Page 333
Excel’s Table Functions......Page 337
What Are PivotTables?......Page 348
Building PivotTables......Page 352
Working with PivotTable Subtotals......Page 357
Changing the Data Field Summary Calculation......Page 358
Creating Custom PivotTable Calculations......Page 367
Budgeting with Calculated Items......Page 372
Using PivotTable Results in a Worksheet Formula......Page 374
Using What-If Analysis......Page 378
Working with Goal Seek......Page 384
Working with Scenarios......Page 391
16 Using Regression to Track Trends and Make Forecasts......Page 402
Using Simple Regression on Linear Data......Page 403
Trend Analysis and Forecasting for a Seasonal Sales Model......Page 417
Using Simple Regression on Nonlinear Data......Page 426
Using Multiple Regression Analysis......Page 440
Some Background on Solver......Page 444
Using Solver......Page 446
Adding Constraints......Page 449
Setting Other Solver Options......Page 451
Making Sense of Solver’s Messages......Page 455
Solving the Transportation Problem......Page 456
Displaying Solver’s Reports......Page 458
IV: BUILDING FINANCIAL FORMULAS......Page 464
Understanding the Time Value of Money......Page 466
Calculating the Loan Payment......Page 467
Building a Loan Amortization Schedule......Page 473
Calculating the Term of the Loan......Page 476
Calculating the Interest Rate Required for a Loan......Page 478
Calculating How Much You Can Borrow......Page 479
Working with Mortgages......Page 480
Working with Interest Rates......Page 486
Calculating the Future Value......Page 489
Working Toward an Investment Goal......Page 491
Building an Investment Schedule......Page 496
20 Building Discount Formulas......Page 500
Calculating the Present Value......Page 501
Discounting Cash Flows......Page 505
Calculating the Payback Period......Page 510
Calculating the Internal Rate of Return......Page 513
Publishing a Book......Page 516
A......Page 522
C......Page 523
D......Page 526
E......Page 527
F......Page 528
I......Page 530
L......Page 531
M......Page 532
P......Page 533
Q-R......Page 534
S......Page 536
T......Page 538
W......Page 539
X-Y-Z......Page 540