Excel Formulas & Functions For Dummies

Ken Bluttman is a veteran software and web developer specializing in Excel/VBA and database-centric web applications. He has written numerous articles and books on a variety of technical topics. His latest projects include large-scale cloud-based applications and mobile app development.

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