Introduction 1
About This Book 1
Foolish Assumptions 2
How to Use This Book 2
Icons Used in This Book 2
Where to Go from Here 3
Part 1: Getting Started with Formulas and Functions 5
Chapter 1: Tapping Into Formula and Function Fundamentals 7
Working with Excel Fundamentals 8
Understanding workbooks and worksheets 8
Introducing the Formulas Tab 11
Working with rows, column, cells, ranges, and tables 13
Formatting your data 17
Getting help 19
Gaining the Upper Hand on Formulas 19
Entering your first formula 20
Understanding references 22
Copying formulas with the fill handle 24
Assembling formulas the right way 25
Using Functions in Formulas 27
Looking at what goes into a function 29
Arguing with a function 30
Nesting functions 32
Chapter 2: Saving Time with Function Tools 37
Getting Familiar with the Insert Function Dialog Box 37
Finding the Correct Function 39
Entering Functions Using the Insert Function Dialog Box 40
Selecting a function that takes no arguments 41
Selecting a function that uses arguments 42
Entering cells, ranges, named areas, and tables as function arguments 43
Getting help in the Insert Function dialog box 48
Using the Function Arguments dialog box to edit functions 49
Directly Entering Formulas and Functions 49
Entering formulas and functions in the Formula Bar 49
Entering formulas and functions directly in worksheet cells 51
Chapter 3: Saying "Array!" for Formulas and Functions 55
Discovering Arrays 56
Using Arrays in Formulas 57
Working with Functions That Return Arrays 61
Chapter 4: Fixing Formula Boo-Boos 65
Catching Errors As You Enter Them 65
Getting parentheses to match 66
Avoiding circular references 68
Mending broken links 70
Using the Formula Error Checker 72
Auditing Formulas 75
Watching the Watch Window 78
Evaluating and Checking Errors 79
Making an Error Behave the Way You Want 81
Part 2: Doing the Math 83
Chapter 5: Calculating Loan Payments and Interest Rates 85
Understanding How Excel Handles Money 86
Going with the cash flow 86
Formatting for currency 86
Choosing separators 88
Figuring Loan Calculations 90
Calculating the payment amount 91
Calculating interest payments 93
Calculating payments toward principal 94
Calculating the number of payments 96
Calculating the number of payments with PDURATION 97
Calculating the interest rate 98
Calculating the principal 101
Chapter 6: Appreciating What You'll Get, Depreciating What You've Got 103
Looking into the Future 104
Depreciating the Finer Things in Life 106
Calculating straight-line depreciation 108
Creating an accelerated depreciation schedule 109
Creating an even faster accelerated depreciation schedule 111
Calculating a midyear depreciation schedule 112
Measuring Your Internals 114
Chapter 7: Using Basic Math Functions 119
Adding It All Together with the SUM Function 119
Rounding Out Your Knowledge 124
Just plain old rounding 124
Rounding in one direction 126
Leaving All Decimals Behind with INT 131
Leaving Some Decimals Behind with TRUNC 132
Looking for a Sign 133
Ignoring Signs 135
Chapter 8: Advancing Your Math 137
Using PI to Calculate Circumference and Diameter 138
Generating and Using Random Numbers 139
The all-purpose RAND function 139
Precise randomness with RANDBETWEEN 141
Ordering Items 143
Combining 144
Raising Numbers to New Heights 145
Multiplying Multiple Numbers 146
Using What Remains with the MOD Function 148
Summing Things Up 149
Using SUBTOTAL 149
Using SUMPRODUCT 151
Using SUMIF and SUMIFS 153
Getting an Angle on Trigonometry 156
Three basic trigonometry functions 156
Degrees and radians 157
Part 3: Solving with Statistics 159
Chapter 9: Throwing Statistics a Curve 161
Getting Stuck in the Middle with AVERAGE, MEDIAN, and MODE 162
Deviating from the Middle 167
Measuring variance 167
Analyzing deviations 170
Looking for normal distribution 172
Skewing from the norm 177
Comparing data sets 179
Analyzing Data with Percentiles and Bins 183
QUARTILE.INC and QUARTILE.EXC 183
PERCENTILE.INC and PERCENTILE.EXC 185
RANK 186
PERCENTRANK 188
FREQUENCY 189
MIN and MAX 191
LARGE and SMALL 193
Going for the Count 195
COUNT and COUNTA 195
COUNTIF 196
Chapter 10: Using Significance Tests 199
Testing to the T 200
Comparing Results with an Estimate 204
Chapter 11: Rolling the Dice on Predictions and Probability 209
Modeling 210
Linear model 210
Exponential model 210
Getting It Straight: Using SLOPE and INTERCEPT to Describe Linear Data 211
What's Ahead: Using FORECAST, TREND, and GROWTH to Make Predictions 215
FORECAST 215
TREND 217
GROWTH 219
Using NORM.DIST and POISSON.DIST to Determine Probabilities 221
NORM.DIST 221
POISSON.DIST 223
Part 4: Dancing with Data 227
Chapter 12: Dressing Up for Date Functions 229
Understanding How Excel Handles Dates 229
Formatting Dates 231
Making a Date with DATE 232
Breaking a Date with DAY, MONTH, and YEAR 234
Isolating the day 234
Isolating the month 236
Isolating the year 237
Converting a Date from Text 237
Finding Out What TODAY Is 239
Counting the days until your birthday 239
Counting your age in days 240
Determining the Day of the Week 240
Working with Workdays 242
Determining workdays in a range of dates 242
Workdays in the future 244
Calculating Time Between Two Dates with the DATEDIF Function 244
Chapter 13: Keeping Well-Timed Functions 247
Understanding How Excel Handles Time 247
Formatting Time 248
Keeping TIME 250
Converting Text to Time with TIMEVALUE 250
Deconstructing Time with HOUR, MINUTE, and SECOND 251
Isolating the hour 252
Isolating the minute 253
Isolating the second 253
Finding the Time NOW 254
Calculating Elapsed Time Over Days 254
Chapter 14: Using Lookup, Logical, and Reference Functions 257
Testing on One Condition 258
Choosing the Right Value 263
Let's Be Logical 265
NOT 265
AND and OR 267
XOR 269
Finding Where the Data Is 270
ADDRESS 270
ROW, ROWS, COLUMN, and COLUMNS 274
OFFSET 276
Looking It Up 278
HLOOKUP and VLOOKUP 278
MATCH and INDEX 281
FORMULATEXT 286
NUMBERVALUE 286
Chapter 15: Digging Up the Facts 289
Getting Informed with the CELL Function 289
Getting Information About Excel and Your Computer System 294
Finding What IS and What IS Not 295
ISERR, ISNA, and ISERROR 296
ISBLANK, ISNONTEXT, ISTEXT, and ISNUMBER 297
Getting to Know Your Type 299
Chapter 16: Writing Home About Text Functions 303
Breaking Apart Text 303
Bearing to the LEFT 304
Swinging to the RIGHT 305
Staying in the MIDdle 306
Finding the long of it with LEN 307
Putting Text Together with CONCATENATE 308
Changing Text 309
Making money 310
Turning numbers into text 311
Repeating text 314
Swapping text 315
Giving text a trim 319
Making a case 320
Comparing, Finding, and Measuring Text 321
Going for perfection with EXACT 321
Finding and searching 323
Chapter 17: Playing Records with Database Functions 327
Putting Your Data into a Database Structure 328
Working with Database Functions 329
Establishing your database 329
Establishing the criteria area 331
Fine-Tuning Criteria with AND and OR 333
Adding Only What Matters with DSUM 335
Going for the Middle with DAVERAGE 335
Counting Only What Matters with DCOUNT 336
Finding Highest and Lowest with DMIN and DMAX 338
Finding Duplicate Values with DGET 339
Being Productive with DPRODUCT 339
Part 5: The Part of Tens 343
Chapter 18: Ten Tips for Working with Formulas 345
Master Operator Precedence 345
Display Formulas 346
Fix Formulas 348
Use Absolute References 349
Turn Calc On/Turn Calc Off 349
Use Named Areas 351
Use Formula Auditing 352
Use Conditional Formatting 353
Use Data Validation 354
Create Your Own Functions 355
Chapter 19: Ten Functions You Really Should Know 359
SUM 360
AVERAGE 360
COUNT 361
INT and ROUND 361
INT 361
ROUND 362
IF 362
NOW and TODAY 363
HLOOKUP and VLOOKUP 363
ISNUMBER 364
MIN and MAX 364
SUMIF and COUNTIF 364
Chapter 20: Ten Really Cool Functions 367
Work with Hexadecimal, Octal, Decimal, and Binary Numbers 368
Convert Units of Measurement 369
Find the Greatest Common Divisor and the Least Common Multiple 370
Easily Generate a Random Number 371
Convert to Roman Numerals 371
Factor in a Factorial 371
Determine Part of a Year with YEARFRAC 372
Find the Data TYPE 372
Find the LENgth of Your Text 373
Just in CASE 373
Index 375
