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
