Formulas and Functions with Microsoft Excel 2003

Cover Page......Page 1
Contents at a Glance......Page 2
ISBN 0789731533......Page 3
1 Getting the Most Out of Ranges......Page 4
3 Building Basic Formulas......Page 5
5 Troubleshooting Formulas......Page 6
7 Working with Text Functions......Page 7
10 Working with Date and Time Functions......Page 8
12 Working with Statistical Functions......Page 9
13 Analyzing Data with Lists......Page 10
15 Using Regression to Track Trends and Make Forecasts......Page 11
17 Building Loan Formulas......Page 12
19 Working with Bonds......Page 13
Index......Page 14
About the Author......Page 15
Acknowledgments......Page 16
We Want to Hear from You!......Page 17
What’s in the Book......Page 18
This Book’s Special Features......Page 19
Mastering Excel Ranges and Formulas......Page 22
A Review of Excel’s Range-Selection Techniques......Page 24
Advanced Range-Selection Techniques......Page 30
Data Entry in a Range......Page 34
Using the Fill Handle......Page 35
Creating a Series......Page 38
Copying a Range......Page 39
Moving a Range......Page 45
Inserting and Deleting a Range......Page 46
Using Excel’s Reference Operators......Page 50
Using Range Names......Page 54
Defining a Range Name......Page 55
Working with Range Names......Page 62
Range Names and the Reference Operators......Page 67
Understanding Formula Basics......Page 70
Understanding Operator Precedence......Page 74
Controlling Worksheet Calculation......Page 76
Copying and Moving Formulas......Page 78
Displaying Worksheet Formulas......Page 81
Converting a Formula to a Value......Page 82
Working with Range Names in Formulas......Page 83
Working with Links in Formulas......Page 87
Formatting Numbers, Dates, and Times......Page 89
Working with Arrays......Page 102
Using Iteration and Circular References......Page 107
Consolidating Multisheet Data......Page 109
Applying Data-Validation Rules to Cells......Page 115
Using Dialog Box Controls on a Worksheet......Page 117
Troubleshooting Formulas......Page 124
Understanding Excel’s Error Values......Page 125
CASE STUDY......Page 127
Fixing Other Formula Errors......Page 129
Using the Formula Error Checker......Page 131
Auditing a Worksheet......Page 134
Harnessing the Power of Functions......Page 140
Using Functions......Page 142
The Structure of a Function......Page 143
Typing a Function into a Formula......Page 145
Using the Insert Function Feature......Page 146
Loading the Analysis ToolPak Functions......Page 148
Working with Text Functions......Page 150
Working with Characters and Codes......Page 152
Converting Text......Page 155
Manipulating Text......Page 159
CASE STUDY......Page 164
CASE STUDY......Page 170
Adding Intelligence with Logical Functions......Page 172
CASE STUDY......Page 185
Getting Data with Information Functions......Page 188
Working with Lookup Functions......Page 198
Understanding Lookup Tables......Page 199
The CHOOSE() Function......Page 200
Looking Up Values in Tables......Page 203
How Excel Deals with Dates and Times......Page 216
Using Excel’s Date Functions......Page 219
Using Excel’s Time Functions......Page 236
CASE STUDY......Page 241
Working with Math Functions......Page 246
Understanding Excel’s Rounding Functions......Page 250
CASE STUDY......Page 255
Summing Values......Page 256
The MOD() Function......Page 258
Generating Random Numbers......Page 261
Working with Statistical Functions......Page 266
Understanding Descriptive Statistics......Page 268
Counting Items with the COUNT() Function......Page 269
Calculating Averages......Page 270
Calculating Extreme Values......Page 272
Calculating Measures of Variation......Page 275
Working with Frequency Distributions......Page 278
Using the Analysis ToolPak Statistical Tools......Page 283
Building Business Models......Page 298
Analyzing Data with Lists......Page 300
Converting a Range to a List......Page 301
Basic List Operations......Page 302
Sorting a List......Page 304
Filtering List Data......Page 309
Creating Automatic Subtotals......Page 318
Excel’s List Functions......Page 323
CASE STUDY......Page 329
Using What-If Analysis......Page 332
Working with Goal Seek......Page 338
Working with Scenarios......Page 347
Using Regression to Track Trends and Make Forecasts......Page 356
Using Simple Regression on Linear Data......Page 357
CASE STUDY......Page 370
Using Simple Regression on Nonlinear Data......Page 377
Using Multiple Regression Analysis......Page 391
Some Background on Solver......Page 394
Using Solver......Page 396
Adding Constraints......Page 399
Setting Other Solver Options......Page 401
Making Sense of Solver’s Messages......Page 405
CASE STUDY......Page 406
Displaying Solver’s Reports......Page 408
Building Financial Formulas......Page 414
Understanding the Time Value of Money......Page 416
Calculating the Loan Payment......Page 417
Building a Loan Amortization Schedule......Page 423
Calculating the Term of the Loan......Page 426
Calculating the Interest Rate Required for a Loan......Page 428
Calculating How Much You Can Borrow......Page 429
CASE STUDY......Page 430
Working with Interest Rates......Page 434
Calculating the Future Value......Page 437
Working Toward an Investment Goal......Page 439
CASE STUDY......Page 443
Glossary of Bond Terms......Page 448
Calculating Bond Yields......Page 449
Calculating Bond Prices......Page 452
Calculating Bond Duration......Page 454
Calculating Bond Principal at Maturity......Page 455
Working with Coupons......Page 456
Working with Zero-Coupon Bonds......Page 460
Working with Treasury Bills......Page 461
Building Discount Formulas......Page 464
Calculating the Present Value......Page 465
Discounting Cash Flows......Page 469
Calculating the Payback Period......Page 474
Calculating the Internal Rate of Return......Page 477
CASE STUDY......Page 480
A......Page 484
B......Page 485
C......Page 486
D......Page 487
E,F......Page 489
G,H,I......Page 491
K,L......Page 492
M......Page 493
N......Page 494
O,P......Page 496
Q,R......Page 497
S......Page 498
T......Page 501
U,V,W......Page 502
X,Y,Z......Page 503