Description
Book SynopsisTable of ContentsIntroduction 1
About This Book 1
Foolish Assumptions 2
Icons Used in This Book 2
Beyond the Book 3
Where to Go from Here 3
Part 1: Getting Started with Excel 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, columns, cells, ranges, and tables 13
Formatting your data 18
Getting help 19
Gaining the Upper Hand on Formulas 20
Entering your first formula 20
Understanding references 22
Copying formulas with the fill handle 25
Assembling formulas the right way 26
Using Functions in Formulas 28
Looking at what goes into a function 30
Arguing with a function 31
Nesting functions 34
Chapter 2: Saving Time with Function Tools 39
Getting Familiar with the Insert Function Dialog Box 39
Finding the Correct Function 41
Entering Functions Using the Insert Function Dialog Box 42
Selecting a function that takes no arguments 43
Selecting a function that uses arguments 44
Entering cells, ranges, named areas, and tables as function arguments 47
Getting help in the Insert Function dialog box 50
Using the Function Arguments dialog box to edit functions 50
Directly Entering Formulas and Functions 51
Entering formulas and functions in the Formula Bar 51
Entering formulas and functions directly in worksheet cells 52
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 98
Calculating the interest rate 99
Calculating the principal 101
Chapter 6: Appreciating What You’ll Get, Depreciating What You’ve Got 105
Looking into the Future 106
Depreciating the Finer Things in Life 108
Calculating straight-line depreciation 110
Creating an accelerated depreciation schedule 111
Creating an even faster accelerated depreciation schedule 113
Calculating a midyear depreciation schedule 114
Measuring Your Internals 116
Chapter 7: Using Basic Math Functions 121
Adding It All Together with the SUM Function 121
Rounding Out Your Knowledge 126
Just plain old rounding 126
Rounding in one direction 128
Leaving All Decimals Behind with INT 133
Leaving Some Decimals Behind with TRUNC 134
Looking for a Sign 135
Ignoring Signs 137
Chapter 8: Advancing Your Math 139
Using PI to Calculate Circumference and Diameter 140
Generating and Using Random Numbers 141
The all-purpose RAND function 141
Precise randomness with RANDBETWEEN 143
Ordering Items 145
Combining 147
Raising Numbers to New Heights 147
Multiplying Multiple Numbers 149
Using What Remains with the MOD Function 150
Summing Things Up 152
Using SUBTOTAL 152
Using SUMPRODUCT 154
Using SUMIF and SUMIFS 156
Getting an Angle on Trigonometry 159
Three basic trigonometry functions 159
Degrees and radians 160
Part 3: Solving with Statistics 161
Chapter 9: Throwing Statistics a Curve 163
Getting Stuck in the Middle with AVERAGE, MEDIAN, and MODE 164
Deviating from the Middle 169
Measuring variance 170
Analyzing deviations 172
Looking for normal distribution 174
Skewing from the norm 179
Comparing data sets 181
Analyzing Data with Percentiles and Bins 185
QUARTILE.INC and QUARTILE.EXC 186
PERCENTILE.INC and PERCENTILE.EXC 187
RANK 189
PERCENTRANK 190
FREQUENCY 191
MIN and MAX 195
LARGE and SMALL 196
Going for the Count 198
COUNT and COUNTA 198
COUNTIF 198
Chapter 10: Using Significance Tests 203
Testing to the T 204
Comparing Results with an Estimate 208
Chapter 11: Rolling the Dice on Predictions and Probability 213
Modeling 214
Linear model 214
Exponential model 214
Getting It Straight: Using SLOPE and INTERCEPT to Describe Linear Data 215
What’s Ahead: Using FORECAST, TREND, and GROWTH to Make Predictions 219
FORECAST 219
TREND 221
GROWTH 223
Using NORM.DIST and POISSON.DIST to Determine Probabilities 225
NORM.DIST 225
POISSON.DIST 227
Part 4: Dancing with Data 231
Chapter 12: Dressing Up for Date Functions 233
Understanding How Excel Handles Dates 233
Formatting Dates 235
Making a Date with DATE 236
Breaking a Date with DAY, MONTH, and YEAR 238
Isolating the day 239
Isolating the month 240
Isolating the year 241
Converting a Date from Text 242
Finding Out What TODAY Is 243
Counting the days until your birthday 244
Counting your age in days 244
Determining the Day of the Week 245
Working with Workdays 246
Determining workdays in a range of dates 246
Workdays in the future 248
Calculating Time Between Two Dates with the DATEDIF Function 249
Chapter 13: Keeping Well-Timed Functions 251
Understanding How Excel Handles Time 251
Formatting Time 252
Keeping TIME 254
Converting Text to Time with TIMEVALUE 254
Deconstructing Time with HOUR, MINUTE, and SECOND 255
Isolating the hour 256
Isolating the minute 257
Isolating the second 258
Finding the Time NOW 258
Calculating Elapsed Time Over Days 259
Chapter 14: Using Lookup, Logical, and Reference Functions 261
Testing on One Condition 262
Choosing the Right Value 267
Let’s Be Logical 269
NOT 270
AND and OR 271
XOR 273
Finding Where the Data Is 275
ADDRESS 275
INDIRECT 279
ROW, ROWS, COLUMN, and COLUMNS 279
OFFSET 282
Looking It Up 284
HLOOKUP and VLOOKUP 284
XLOOKUP 288
MATCH and INDEX 289
FORMULATEXT 294
NUMBERVALUE 295
Chapter 15: Digging Up the Facts 297
Getting Informed with the CELL Function 297
Getting Information About Excel and Your Computer System 302
Finding What IS and What IS Not 304
ISERR, ISNA, and ISERROR 304
ISBLANK, ISNONTEXT, ISTEXT, and ISNUMBER 306
Getting to Know Your Type 308
Chapter 16: Writing Home about Text Functions 311
Breaking Apart Text 311
Bearing to the LEFT 312
Swinging to the RIGHT 313
Staying in the MIDdle 314
Finding the long of it with LEN 315
Putting Text Together with CONCATENATE 316
Changing Text 318
Making money 318
Turning numbers into text 320
Repeating text 323
Swapping text 324
Giving text a trim 328
Making a case 329
Comparing, Finding, and Measuring Text 330
Going for perfection with EXACT 331
Finding and searching 332
Chapter 17: Playing Records with Database Functions 337
Putting Your Data into a Database Structure 338
Working with Database Functions 339
Establishing your database 339
Establishing the criteria area 341
Fine-Tuning Criteria with AND and OR 343
Adding Only What Matters with DSUM 345
Going for the Middle with DAVERAGE 345
Counting Only What Matters with DCOUNT 347
Finding Highest and Lowest with DMIN and DMAX 348
Finding Duplicate Values with DGET 349
Being Productive with DPRODUCT 350
Part 5: The Part of Tens 353
Chapter 18: Ten Tips for Working with Formulas 355
Master Operator Precedence 355
Display Formulas 356
Fix Formulas 358
Use Absolute References 359
Turn Calc On/Turn Calc Off 359
Use Named Areas 361
Use Formula Auditing 362
Use Conditional Formatting 363
Use Data Validation 364
Create Your Own Functions 365
Chapter 19: Ten Ways to Get Fancy with Excel 369
Calculating Data from Multiple Sheets 369
Getting Data from the Internet 370
Determining the Needed Number 370
Removing Duplicates 371
Getting to the Last Row of Your Data 372
Freezing Panes 372
Splitting a Worksheet 372
Filling Cells 373
Adding Notes to Cells 373
Getting More Information about a Workbook or Worksheet 374
Chapter 20: Ten Really Cool Functions 375
Work with Hexadecimal, Octal, Decimal, and Binary Numbers 376
Convert Units of Measurement 377
Find the Greatest Common Divisor and the Least Common Multiple 378
Easily Generate a Random Number 379
Convert to Roman Numerals 379
Factor in a Factorial 379
Determine Part of a Year with YEARFRAC 380
Find the Data TYPE 380
Find the LENgth of Your Text 381
Just in CASE 381
Index 383
