Excel Formulas and Functions

Part I: Putting the Fun in Functions - Page 25
Part I: Putting the Fun in Functions - Page 26
Chapter 1: Tapping into Formula and Function Fundamentals - Page 27
Working with Excel Fundamentals - Page 28
Working with Excel Fundamentals - Page 29
Working with Excel Fundamentals - Page 30
Working with Excel Fundamentals - Page 31
Working with Excel Fundamentals - Page 32
Working with Excel Fundamentals - Page 33
Working with Excel Fundamentals - Page 34
Working with Excel Fundamentals - Page 35
Working with Excel Fundamentals - Page 36
Working with Excel Fundamentals - Page 37
Working with Excel Fundamentals - Page 38
Gaining the Upper Hand on Formulas - Page 39
Gaining the Upper Hand on Formulas - Page 40
Gaining the Upper Hand on Formulas - Page 41
Gaining the Upper Hand on Formulas - Page 42
Gaining the Upper Hand on Formulas - Page 43
Gaining the Upper Hand on Formulas - Page 44
Gaining the Upper Hand on Formulas - Page 45
Gaining the Upper Hand on Formulas - Page 46
Gaining the Upper Hand on Formulas - Page 47
Using Functions in Formulas - Page 48
Using Functions in Formulas - Page 49
Using Functions in Formulas - Page 50
Using Functions in Formulas - Page 51
Using Functions in Formulas - Page 52
Using Functions in Formulas - Page 53
Using Functions in Formulas - Page 54
Using Functions in Formulas - Page 55
Using Functions in Formulas - Page 56
Using Functions in Formulas - Page 57
Using Functions in Formulas - Page 58
Chapter 2: Saving Time with Function Tools - Page 59
Chapter 2: Saving Time with Function Tools - Page 60
Finding the Correct Function - Page 61
Entering Functions Using the Insert Function Dialog Box - Page 62
Entering Functions Using the Insert Function Dialog Box - Page 63
Entering Functions Using the Insert Function Dialog Box - Page 64
Entering Functions Using the Insert Function Dialog Box - Page 65
Entering Functions Using the Insert Function Dialog Box - Page 66
Entering Functions Using the Insert Function Dialog Box - Page 67
Entering Functions Using the Insert Function Dialog Box - Page 68
Entering Functions Using the Insert Function Dialog Box - Page 69
Entering Functions Using the Insert Function Dialog Box - Page 70
Entering Functions Using the Insert Function Dialog Box - Page 71
Directly Entering Formulas and Functions - Page 72
Directly Entering Formulas and Functions - Page 73
Directly Entering Formulas and Functions - Page 74
Directly Entering Formulas and Functions - Page 75
Directly Entering Formulas and Functions - Page 76
Chapter 3: Saying “Array!” for Formulas and Functions - Page 77
Chapter 3: Saying “Array!” for Formulas and Functions - Page 78
Using Arrays in Formulas - Page 79
Using Arrays in Formulas - Page 80
Using Arrays in Formulas - Page 81
Using Arrays in Formulas - Page 82
Working with Functions That Return Arrays - Page 83
Working with Functions That Return Arrays - Page 84
Working with Functions That Return Arrays - Page 85
Working with Functions That Return Arrays - Page 86
Working with Functions That Return Arrays - Page 87
Working with Functions That Return Arrays - Page 88
Chapter 4: Fixing Formula Boo-Boos - Page 89
Chapter 4: Fixing Formula Boo-Boos - Page 90
Chapter 4: Fixing Formula Boo-Boos - Page 91
Chapter 4: Fixing Formula Boo-Boos - Page 92
Chapter 4: Fixing Formula Boo-Boos - Page 93
Chapter 4: Fixing Formula Boo-Boos - Page 94
Chapter 4: Fixing Formula Boo-Boos - Page 95
Chapter 4: Fixing Formula Boo-Boos - Page 96
Chapter 4: Fixing Formula Boo-Boos - Page 97
Chapter 4: Fixing Formula Boo-Boos - Page 98
Auditing Formulas - Page 99
Auditing Formulas - Page 100
Auditing Formulas - Page 101
Watching the Watch Window - Page 102
Evaluating and Checking Errors - Page 103
Making an Error Behave the Way You Want - Page 104
Making an Error Behave the Way You Want - Page 105
Making an Error Behave the Way You Want - Page 106
Part II: Doing the Math - Page 107
Part II: Doing the Math - Page 108
Chapter 5: Calculating Loan Payments and Interest Rates - Page 109
Understanding How Excel Handles Money - Page 110
Understanding How Excel Handles Money - Page 111
Understanding How Excel Handles Money - Page 112
Understanding How Excel Handles Money - Page 113
Figuring Loan Calculations - Page 114
Figuring Loan Calculations - Page 115
Figuring Loan Calculations - Page 116
Figuring Loan Calculations - Page 117
Figuring Loan Calculations - Page 118
Figuring Loan Calculations - Page 119
Figuring Loan Calculations - Page 120
Figuring Loan Calculations - Page 121
Figuring Loan Calculations - Page 122
Figuring Loan Calculations - Page 123
Figuring Loan Calculations - Page 124
Figuring Loan Calculations - Page 125
Figuring Loan Calculations - Page 126
Figuring Loan Calculations - Page 127
Figuring Loan Calculations - Page 128
Chapter 6: Appreciating What You’ll Get, Depreciating What You’ve Got - Page 129
Looking into the Future - Page 130
Looking into the Future - Page 131
Depreciating the Finer Things in Life - Page 132
Depreciating the Finer Things in Life - Page 133
Depreciating the Finer Things in Life - Page 134
Depreciating the Finer Things in Life - Page 135
Depreciating the Finer Things in Life - Page 136
Depreciating the Finer Things in Life - Page 137
Depreciating the Finer Things in Life - Page 138
Depreciating the Finer Things in Life - Page 139
Depreciating the Finer Things in Life - Page 140
Measuring Your Internals - Page 141
Measuring Your Internals - Page 142
Measuring Your Internals - Page 143
Measuring Your Internals - Page 144
Chapter 7: Using Basic Math Functions - Page 145
Chapter 7: Using Basic Math Functions - Page 146
Chapter 7: Using Basic Math Functions - Page 147
Chapter 7: Using Basic Math Functions - Page 148
Chapter 7: Using Basic Math Functions - Page 149
Rounding Out Your Knowledge - Page 150
Rounding Out Your Knowledge - Page 151
Rounding Out Your Knowledge - Page 152
Rounding Out Your Knowledge - Page 153
Rounding Out Your Knowledge - Page 154
Rounding Out Your Knowledge - Page 155
Rounding Out Your Knowledge - Page 156
Leaving All Decimals Behind with INT - Page 157
Leaving All Decimals Behind with INT - Page 158
Leaving Some Decimals Behind with TRUNC - Page 159
Looking for a Sign - Page 160
Looking for a Sign - Page 161
Ignoring Signs - Page 162
Chapter 8: Advancing Your Math - Page 163
Generating and Using Random Numbers - Page 164
Generating and Using Random Numbers - Page 165
Generating and Using Random Numbers - Page 166
Generating and Using Random Numbers - Page 167
Ordering Items - Page 168
Ordering Items - Page 169
Combining - Page 170
Combining - Page 171
Multiplying Multiple Numbers - Page 172
Using What Remains with the MOD Function - Page 173
Using What Remains with the MOD Function - Page 174
Summing Things Up - Page 175
Summing Things Up - Page 176
Summing Things Up - Page 177
Summing Things Up - Page 178
Summing Things Up - Page 179
Summing Things Up - Page 180
Summing Things Up - Page 181
Getting an Angle on Trigonometry - Page 182
Getting an Angle on Trigonometry - Page 183
Getting an Angle on Trigonometry - Page 184
Part III: Solving with Statistics - Page 185
Part III: Solving with Statistics - Page 186
Chapter 9: Throwing Statistics a Curve - Page 187
Stuck in the Middle with AVERAGE, MEDIAN, and MODE - Page 188
Stuck in the Middle with AVERAGE, MEDIAN, and MODE - Page 189
Stuck in the Middle with AVERAGE, MEDIAN, and MODE - Page 190
Stuck in the Middle with AVERAGE, MEDIAN, and MODE - Page 191
Stuck in the Middle with AVERAGE, MEDIAN, and MODE - Page 192
Deviating from the Middle - Page 193
Deviating from the Middle - Page 194
Deviating from the Middle - Page 195
Deviating from the Middle - Page 196
Deviating from the Middle - Page 197
Deviating from the Middle - Page 198
Deviating from the Middle - Page 199
Deviating from the Middle - Page 200
Deviating from the Middle - Page 201
Deviating from the Middle - Page 202
Deviating from the Middle - Page 203
Deviating from the Middle - Page 204
Deviating from the Middle - Page 205
Deviating from the Middle - Page 206
Deviating from the Middle - Page 207
Deviating from the Middle - Page 208
Analyzing Data with Percentiles and Bins - Page 209
Analyzing Data with Percentiles and Bins - Page 210
Analyzing Data with Percentiles and Bins - Page 211
Analyzing Data with Percentiles and Bins - Page 212
Analyzing Data with Percentiles and Bins - Page 213
Analyzing Data with Percentiles and Bins - Page 214
Analyzing Data with Percentiles and Bins - Page 215
Analyzing Data with Percentiles and Bins - Page 216
Analyzing Data with Percentiles and Bins - Page 217
Analyzing Data with Percentiles and Bins - Page 218
Analyzing Data with Percentiles and Bins - Page 219
Analyzing Data with Percentiles and Bins - Page 220
Going for the Count - Page 221
Going for the Count - Page 222
Going for the Count - Page 223
Going for the Count - Page 224
Chapter 10: Using Significance Tests - Page 225
Testing to the T - Page 226
Testing to the T - Page 227
Testing to the T - Page 228
Testing to the T - Page 229
Comparing Results to an Estimate - Page 230
Comparing Results to an Estimate - Page 231
Comparing Results to an Estimate - Page 232
Comparing Results to an Estimate - Page 233
Comparing Results to an Estimate - Page 234
Chapter 11: Rolling the Dice on Predictions and Probability - Page 235
Chapter 11: Rolling the Dice on Predictions and Probability - Page 236
Getting It Straight: Using SLOPE and INTERCEPT to Describe Linear Data - Page 237
Getting It Straight: Using SLOPE and INTERCEPT to Describe Linear Data - Page 238
Getting It Straight: Using SLOPE and INTERCEPT to Describe Linear Data - Page 239
Getting It Straight: Using SLOPE and INTERCEPT to Describe Linear Data - Page 240
What’s in the Future: Using FORECAST, TREND, and GROWTH to Make Predictions - Page 241
What’s in the Future: Using FORECAST, TREND, and GROWTH to Make Predictions - Page 242
What’s in the Future: Using FORECAST, TREND, and GROWTH to Make Predictions - Page 243
What’s in the Future: Using FORECAST, TREND, and GROWTH to Make Predictions - Page 244
What’s in the Future: Using FORECAST, TREND, and GROWTH to Make Predictions - Page 245
Using NORM. DIST and POISSON. DIST to Determine Probabilities - Page 246
Using NORM. DIST and POISSON. DIST to Determine Probabilities - Page 247
Using NORM. DIST and POISSON. DIST to Determine Probabilities - Page 248
Using NORM. DIST and POISSON. DIST to Determine Probabilities - Page 249
Using NORM. DIST and POISSON. DIST to Determine Probabilities - Page 250
Using NORM. DIST and POISSON. DIST to Determine Probabilities - Page 251
Using NORM. DIST and POISSON. DIST to Determine Probabilities - Page 252
Part IV: Working with Data - Page 253
Part IV: Working with Data - Page 254
Chapter 12: Dressing Up for Date Functions - Page 255
Chapter 12: Dressing Up for Date Functions - Page 256
Formatting Dates - Page 257
Making a Date with DATE - Page 258
Making a Date with DATE - Page 259
Breaking a Date with DAY, MONTH, and YEAR - Page 260
Breaking a Date with DAY, MONTH, and YEAR - Page 261
Breaking a Date with DAY, MONTH, and YEAR - Page 262
Converting a Date from Text - Page 263
Converting a Date from Text - Page 264
Finding Out What TODAY Is - Page 265
Determining the Day of the Week - Page 266
Determining the Day of the Week - Page 267
Working with Workdays - Page 268
Working with Workdays - Page 269
Working with Workdays - Page 270
Calculating Time between Two Dates with the DATEDIF Function - Page 271
Calculating Time between Two Dates with the DATEDIF Function - Page 272
Calculating Time between Two Dates with the DATEDIF Function - Page 273
Calculating Time between Two Dates with the DATEDIF Function - Page 274
Chapter 13: Keeping Well-Timed Functions - Page 275
Formatting Time - Page 276
Formatting Time - Page 277
Keeping TIME - Page 278
Deconstructing Time with HOUR, MINUTE, and SECOND - Page 279
Deconstructing Time with HOUR, MINUTE, and SECOND - Page 280
Deconstructing Time with HOUR, MINUTE, and SECOND - Page 281
Finding the Time NOW - Page 282
Calculating Elapsed Time over Days - Page 283
Calculating Elapsed Time over Days - Page 284
Chapter 14: Using Lookup, Logical, and Reference Functions - Page 285
Testing on One Condition - Page 286
Testing on One Condition - Page 287
Testing on One Condition - Page 288
Testing on One Condition - Page 289
Testing on One Condition - Page 290
Choosing the Right Value - Page 291
Choosing the Right Value - Page 292
Let’s Be Logical - Page 293
Let’s Be Logical - Page 294
Let’s Be Logical - Page 295
Let’s Be Logical - Page 296
Let’s Be Logical - Page 297
Finding Where It Is - Page 298
Finding Where It Is - Page 299
Finding Where It Is - Page 300
Finding Where It Is - Page 301
Finding Where It Is - Page 302
Finding Where It Is - Page 303
Finding Where It Is - Page 304
Finding Where It Is - Page 305
Looking It Up - Page 306
Looking It Up - Page 307
Looking It Up - Page 308
Looking It Up - Page 309
Looking It Up - Page 310
Looking It Up - Page 311
Looking It Up - Page 312
Looking It Up - Page 313
Looking It Up - Page 314
Looking It Up - Page 315
Looking It Up - Page 316
Chapter 15: Digging Up the Facts - Page 317
Chapter 15: Digging Up the Facts - Page 318
Chapter 15: Digging Up the Facts - Page 319
Chapter 15: Digging Up the Facts - Page 320
Chapter 15: Digging Up the Facts - Page 321
Chapter 15: Digging Up the Facts - Page 322
Getting Information about Excel and Your Computer System - Page 323
Finding What IS and What IS Not - Page 324
Finding What IS and What IS Not - Page 325
Finding What IS and What IS Not - Page 326
Finding What IS and What IS Not - Page 327
Getting to Know Your Type - Page 328
Getting to Know Your Type - Page 329
Getting to Know Your Type - Page 330
Chapter 16: Writing Home about Text Functions - Page 331
Chapter 16: Writing Home about Text Functions - Page 332
Chapter 16: Writing Home about Text Functions - Page 333
Chapter 16: Writing Home about Text Functions - Page 334
Chapter 16: Writing Home about Text Functions - Page 335
Putting Text Together with CONCATENATE - Page 336
Changing Text - Page 337
Changing Text - Page 338
Changing Text - Page 339
Changing Text - Page 340
Changing Text - Page 341
Changing Text - Page 342
Changing Text - Page 343
Changing Text - Page 344
Changing Text - Page 345
Changing Text - Page 346
Changing Text - Page 347
Changing Text - Page 348
Changing Text - Page 349
Comparing, Finding, and Measuring Text - Page 350
Comparing, Finding, and Measuring Text - Page 351
Comparing, Finding, and Measuring Text - Page 352
Comparing, Finding, and Measuring Text - Page 353
Comparing, Finding, and Measuring Text - Page 354
Comparing, Finding, and Measuring Text - Page 355
Comparing, Finding, and Measuring Text - Page 356
Chapter 17: Playing Records with Database Functions - Page 357
Chapter 17: Playing Records with Database Functions - Page 358
Working with Database Functions - Page 359
Working with Database Functions - Page 360
Working with Database Functions - Page 361
Working with Database Functions - Page 362
Fine-tuning Criteria with AND and OR - Page 363
Fine-tuning Criteria with AND and OR - Page 364
Adding Only What Matters with DSUM - Page 365
Counting Only What Matters with DCOUNT - Page 366
Counting Only What Matters with DCOUNT - Page 367
Finding Highest and Lowest with DMIN and DMAX - Page 368
Being Productive with DPRODUCT - Page 369
Being Productive with DPRODUCT - Page 370
Part V: The Part of Tens - Page 371
Part V: The Part of Tens - Page 372
Chapter 18: Ten Tips for Working with Formulas - Page 373
Display Formulas - Page 374
Fixing Formulas - Page 375
Use Absolute References - Page 376
Turn Calc On/Turn Calc Off - Page 377
Use Named Areas - Page 378
Use Formula Auditing - Page 379
Use Conditional Formatting - Page 380
Use Data Validation - Page 381
Create Your Own Functions - Page 382
Create Your Own Functions - Page 383
Create Your Own Functions - Page 384
Chapter 19: Ten Functions You Really Should Know - Page 385
AVERAGE - Page 386
INT and ROUND - Page 387
IF - Page 388
HLOOKUP and VLOOKUP - Page 389
SUMIF and COUNTIF - Page 390
Chapter 20: Some Really Cool Functions - Page 391
Chapter 20: Some Really Cool Functions - Page 392
Convert Units of Measurement - Page 393
Easy Random Number Generation - Page 394
Factoring in a Factorial - Page 395