Excel Formulas & Functions For Dummies

UNLOCK THE POWER OF EXCEL WITH A STEP-BY-STEP ROADMAP TO ITS FORMULAS AND FUNCTIONS

There's a Swiss Army knife in your digital toolbox that can multiply your productivity and make you the smartest guy or gal in almost any room. It's called Microsoft Excel. If you're like most people, you've barely scratched the surface of what this powerful tool's hundreds of built-in functions can do. But with a little help from Excel Formulas & Functions For Dummies, you'll soon be organizing, analyzing, and interpreting data like a pro. For those who don't know the difference between a spreadsheet and a bedsheet, the book gets you up to speed with formula and function basics first. But you can also skip ahead to the fancy stuff and learn about working with probabilities, significance tests, and lookup functions. This easy-to-use Excel formulas and functions survival guide shows you how to:

* Work with financial functions like PMT, PPMT, NPER, RATE, and PV
* Calculate mean, median, mode, standard deviation, and many more statistical functions
* Troubleshoot formulas for common errors and validate your data to avoid mistakes
* Work with dates, times, logic operators, conditions, and basic and advanced mathematical functions

You don't need a degree in data science or advanced mathematics to take advantage of the full functionality and flexibility of Microsoft Excel. Let Excel Formulas & Functions For Dummies show you how to transform this unassuming program into the most useful tool in your toolbox. KEN BLUTTMAN is a veteran software and web developer specializing in Excel/VBA and database-centric web applications. He has written articles and books on topics like Office/VBA development, XML, SQL Server, and InfoPath. Ken is the author of Excel Charts For Dummies and all previous editions of Excel Formulas & Functions For Dummies.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

Work with Hexadecimal, Octal, Decimal, and Binary Numbers 376

Convert Units of Measurement 377

Find the Greatest Common Divisor and the Least Common Multiple 378

Easily Generate a Random Number 379

Convert to Roman Numerals 379

Factor in a Factorial 379

Determine Part of a Year with YEARFRAC 380

Find the Data TYPE 380

Find the LENgth of Your Text 381

Just in CASE 381

Index 383