Excel Formulas & Functions For Dummies, 5th Ed.

Title Page
......Page 4
Copyright Page......Page 5
Table of Contents......Page 8
About This Book......Page 16
Icons Used in This Book......Page 17
Where to Go from Here......Page 18
Part 1 Getting Started with Formulas and Functions......Page 20
Chapter 1 Tapping Into Formula and Function Fundamentals......Page 22
Understanding workbooks and worksheets......Page 23
Introducing the Formulas Tab......Page 26
Working with rows, column, cells, ranges, and tables......Page 28
Formatting your data......Page 32
Gaining the Upper Hand on Formulas......Page 34
Entering your first formula......Page 35
Understanding references......Page 37
Copying formulas with the fill handle......Page 39
Assembling formulas the right way......Page 40
Using Functions in Formulas......Page 42
Looking at what goes into a function......Page 44
Arguing with a function......Page 45
Nesting functions......Page 47
Getting Familiar with the Insert Function Dialog Box......Page 52
Finding the Correct Function......Page 54
Entering Functions Using the Insert Function Dialog Box......Page 55
Selecting a function that takes no arguments......Page 56
Selecting a function that uses arguments......Page 57
Entering cells, ranges, named areas, and tables as function arguments......Page 58
Getting help in the Insert Function dialog box......Page 63
Entering formulas and functions in the Formula Bar......Page 64
Entering formulas and functions directly in worksheet cells......Page 66
Chapter 3 Saying “Array!” for Formulas and Functions......Page 70
Discovering Arrays......Page 71
Using Arrays in Formulas......Page 72
Working with Functions That Return Arrays......Page 76
Catching Errors As You Enter Them......Page 80
Getting parentheses to match......Page 81
Avoiding circular references......Page 83
Mending broken links......Page 85
Using the Formula Error Checker......Page 87
Auditing Formulas......Page 90
Watching the Watch Window......Page 93
Evaluating and Checking Errors......Page 94
Making an Error Behave the Way You Want......Page 96
Part 2 Doing the Math......Page 98
Chapter 5 Calculating Loan Payments and Interest Rates......Page 100
Formatting for currency......Page 101
Choosing separators......Page 103
Figuring Loan Calculations......Page 105
Calculating the payment amount......Page 106
Calculating interest payments......Page 108
Calculating payments toward principal......Page 109
Calculating the number of payments......Page 111
Calculating the number of payments with PDURATION......Page 112
Calculating the interest rate......Page 113
Calculating the principal......Page 116
Chapter 6 Appreciating What You’ll Get, Depreciating What You’ve Got......Page 118
Looking into the Future......Page 119
Depreciating the Finer Things in Life......Page 121
Calculating straight-line depreciation......Page 123
Creating an accelerated depreciation schedule......Page 124
Creating an even faster accelerated depreciation schedule......Page 126
Calculating a midyear depreciation schedule......Page 127
Measuring Your Internals......Page 129
Adding It All Together with the SUM Function......Page 134
Just plain old rounding......Page 139
Rounding in one direction......Page 141
Leaving All Decimals Behind with INT......Page 146
Leaving Some Decimals Behind with TRUNC......Page 147
Looking for a Sign......Page 148
Ignoring Signs......Page 150
Chapter 8 Advancing Your Math......Page 152
Using PI to Calculate Circumference and Diameter......Page 153
The all-purpose RAND function......Page 154
Precise randomness with RANDBETWEEN......Page 156
Ordering Items......Page 158
Combining......Page 159
Raising Numbers to New Heights......Page 160
Multiplying Multiple Numbers......Page 161
Using What Remains with the MOD Function......Page 163
Using SUBTOTAL......Page 164
Using SUMPRODUCT......Page 166
Using SUMIF and SUMIFS......Page 168
Three basic trigonometry functions......Page 171
Degrees and radians......Page 172
Part 3 Solving with Statistics......Page 174
Chapter 9 Throwing Statistics a Curve......Page 176
Getting Stuck in the Middle with AVERAGE, MEDIAN, and MODE......Page 177
Measuring variance......Page 182
Analyzing deviations......Page 185
Looking for normal distribution......Page 187
Skewing from the norm......Page 192
Comparing data sets......Page 194
QUARTILE.INC and QUARTILE.EXC......Page 198
PERCENTILE.INC and PERCENTILE.EXC......Page 200
RANK......Page 201
PERCENTRANK......Page 203
FREQUENCY......Page 204
MIN and MAX......Page 206
LARGE and SMALL......Page 208
COUNT and COUNTA......Page 210
COUNTIF......Page 211
Chapter 10 Using Significance Tests......Page 214
Testing to the T......Page 215
Comparing Results with an Estimate......Page 219
Chapter 11 Rolling the Dice on Predictions and Probability......Page 224
Exponential model......Page 225
Getting It Straight: Using SLOPE and INTERCEPT to Describe Linear Data......Page 226
FORECAST......Page 230
TREND......Page 232
GROWTH......Page 234
NORM.DIST......Page 236
POISSON.DIST......Page 238
Part 4 Dancing with Data......Page 242
Understanding How Excel Handles Dates......Page 244
Formatting Dates......Page 246
Making a Date with DATE......Page 247
Isolating the day......Page 249
Isolating the month......Page 251
Converting a Date from Text......Page 252
Counting the days until your birthday......Page 254
Determining the Day of the Week......Page 255
Determining workdays in a range of dates......Page 257
Calculating Time Between Two Dates with the DATEDIF Function......Page 259
Understanding How Excel Handles Time......Page 262
Formatting Time......Page 263
Converting Text to Time with TIMEVALUE......Page 265
Deconstructing Time with HOUR, MINUTE, and SECOND......Page 266
Isolating the hour......Page 267
Isolating the second......Page 268
Calculating Elapsed Time Over Days......Page 269
Chapter 14 Using Lookup, Logical, and Reference Functions......Page 272
Testing on One Condition......Page 273
Choosing the Right Value......Page 278
NOT......Page 280
AND and OR......Page 282
XOR......Page 284
ADDRESS......Page 285
ROW, ROWS, COLUMN, and COLUMNS......Page 289
OFFSET......Page 291
HLOOKUP and VLOOKUP......Page 293
MATCH and INDEX......Page 296
NUMBERVALUE......Page 301
Getting Informed with the CELL Function......Page 304
Getting Information About Excel and Your Computer System......Page 309
Finding What IS and What IS Not......Page 310
ISERR, ISNA, and ISERROR......Page 311
ISBLANK, ISNONTEXT, ISTEXT, and ISNUMBER......Page 312
Getting to Know Your Type......Page 314
Breaking Apart Text......Page 318
Bearing to the LEFT......Page 319
Swinging to the RIGHT......Page 320
Staying in the MIDdle......Page 321
Finding the long of it with LEN......Page 322
Putting Text Together with CONCATENATE......Page 323
Changing Text......Page 324
Making money......Page 325
Turning numbers into text......Page 326
Repeating text......Page 329
Swapping text......Page 330
Giving text a trim......Page 334
Making a case......Page 335
Going for perfection with EXACT......Page 336
Finding and searching......Page 338
Chapter 17 Playing Records with Database Functions......Page 342
Putting Your Data into a Database Structure......Page 343
Establishing your database......Page 344
Establishing the criteria area......Page 346
Fine-Tuning Criteria with AND and OR......Page 348
Going for the Middle with DAVERAGE......Page 350
Counting Only What Matters with DCOUNT......Page 351
Finding Highest and Lowest with DMIN and DMAX......Page 353
Being Productive with DPRODUCT......Page 354
Part 5 The Part of Tens......Page 358
Master Operator Precedence......Page 360
Display Formulas......Page 361
Fix Formulas......Page 363
Turn Calc On/Turn Calc Off......Page 364
Use Named Areas......Page 366
Use Formula Auditing......Page 367
Use Conditional Formatting......Page 368
Use Data Validation......Page 369
Create Your Own Functions......Page 370
Chapter 19 Ten Functions You Really Should Know......Page 374
AVERAGE......Page 375
INT......Page 376
IF......Page 377
HLOOKUP and VLOOKUP......Page 378
SUMIF and COUNTIF......Page 379
Chapter 20 Ten Really Cool Functions......Page 382
Work with Hexadecimal, Octal, Decimal, and Binary Numbers......Page 383
Convert Units of Measurement......Page 384
Find the Greatest Common Divisor and the Least Common Multiple......Page 385
Factor in a Factorial......Page 386
Find the Data TYPE......Page 387
Just in CASE......Page 388
Index......Page 390
EULA......Page 0