Introduction 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