Excel Formulas and Functions For Dummies Book

Introduction 1

About This Book 1

How to Use This Book 1

What You Can Safely Ignore 2

Foolish Assumptions 2

How This Book Is Organized 2

Part I: Putting the Fun in Functions 3

Part II: Doing the Math 3

Part III: Solving with Statistics 3

Part IV: Working with Data 3

Part V: The Part of Tens 4

Icons Used in This Book 4

Where to Go from Here 4

Part I: Putting the Fun in Functions 5

Chapter 1: Tapping into Formula and Function Fundamentals 7

Working with Excel Fundamentals 8

Understanding workbooks and worksheets 8

Introducing the Formulas Ribbon 11

Working with rows, column, cells, ranges, and tables 13

Formatting your data 17

Getting help 18

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 28

Looking at what goes into a function 29

Arguing with a function 30

Nesting functions 34

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 43

Entering cells, ranges, named areas, and tables as function arguments 44

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 55

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 74

Watching the Watch Window 77

Evaluating and Checking Errors 78

Making an Error Behave the Way You Want 80

Part II: 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 interest rate 98

Calculating the principal 100

Chapter 6: Appreciating What You'll Get, Depreciating What You've Got 103

Looking into the Future 103

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 mid-year depreciation schedule 113

Measuring Your Internals 115

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 133

Looking for a Sign 134

Ignoring Signs 136

Chapter 8: Advancing Your Math 137

Using PI to Calculate Circumference and Diameter 137

Generating and Using Random Numbers 138

Ordering Items 142

Combining 143

Raising Numbers to New Heights 144

Multiplying Multiple Numbers 145

Using What Remains with the MOD Function 147

Summing Things Up 148

Using SUBTOTAL 148

Using SUMPRODUCT 151

Using SUMIF and SUMIFS 153

Getting an Angle on Trigonometry 155

Three basic trigonometry functions 155

Degrees and radians 156

Part III: Solving with Statistics 159

Chapter 9: Throwing Statistics a Curve 161

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

Skewed from the norm 176

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 192

LARGE and SMALL 193

Going for the Count 195

COUNT 195

COUNTIF 196

Chapter 10: Using Significance Tests 199

Testing to the T 200

Comparing Results to an Estimate 204

Chapter 11: Rolling the Dice on Predictions and Probability 209

Modeling 209

Linear model 210

Exponential model 210

Getting It Straight: Using SLOPE and INTERCEPT to Describe Linear Data 211

What's in the Future: Using FORECAST, TREND, and GROWTH to Make Predictions 214

FORECAST 215

TREND 216

GROWTH 218

Using NORM.DIST and POISSON.DIST to Determine Probabilities 220

NORMDIST 220

POISSON.DIST 222

Part IV: Working 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 249

Text to Time with TIMEVALUE 250

Deconstructing Time with HOUR, MINUTE, and SECOND 251

Isolating the hour 251

Isolating the minute 252

Isolating the second 253

Finding the Time NOW 253

Calculating Elapsed Time over Days 254

Chapter 14: Using Lookup, Logical, and Reference Functions 257

Testing on One Condition 257

Choosing the Right Value 263

Let's Be Logical 264

NOT 265

AND and OR 266

Finding Where It Is 268

ADDRESS 268

ROW, ROWS, COLUMN, and COLUMNS 272

OFFSET 274

Looking It Up 276

HLOOKUP and VLOOKUP 276

MATCH 280

Chapter 15: Digging Up the Facts 285

Getting Informed with the CELL Function 285

Getting Information about Excel and Your Computer System 291

Finding What IS and What IS Not 292

ISERR, ISERROR, and ISNA 293

ISBLANK, ISNONTEXT, ISTEXT, and ISNUMBER 294

Getting to Know Your Type 296

Chapter 16: Writing Home about Text Functions 299

Breaking Apart Text 299

Bearing to the LEFT 300

Swinging to the RIGHT 301

Staying in the MIDdle 302

Finding the long of it with LEN 303

Putting Text Together with CONCATENATE 304

Changing Text 306

Making money 306

Turning numbers into text 308

Repeating text 310

Swapping text 311

Giving text a trim 315

Making a case 316

Comparing, Finding, and Measuring Text 318

Going for perfection with EXACT 318

Finding and searching 319

Chapter 17: Playing Records with Database Functions 325

Putting Your Data into a Database Structure 325

Working with Database Functions 327

Establishing your database 327

Establishing the criteria area 329

Fine-Tuning Criteria with AND and OR 331

Adding Only What Matters with DSUM 333

Going for the Middle with DAVERAGE 333

Counting Only What Matters with DCOUNT 334

Finding Highest and Lowest with DMIN and DMAX 336

Finding Duplicate Values with DGET 336

Part V: The Part of Tens 339

Chapter 18: Ten Tips for Working with Formulas 341

Operator Precedence 341

Display Formulas 342

Fixing Formulas 343

Use Absolute References 344

Turn Calc On/Turn Calc Off 345

Use Named Areas 346

Use Formula Auditing 347

Use Conditional Formatting 348

Use Data Validation 349

Create Your Own Functions 350

Chapter 19: Ten Functions You Really Should Know 353

Sum 353

Average 354

Count 354

Int and Round 355

Int 355

Round 355

If 356

Now and today 356

Hlookup and Vlookup 357

Isnumber 357

Min and Max 357

Sumif and Countif 358

Chapter 20: Some Really Cool Functions 359

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

Convert Units of Measurement 361

Find the Greatest Common Divisor and the Least Common Multiple 361

Easy Random Number Generation 362

Converting to Roman Numerals 362

Factoring in a Factorial 362

Determining Part of a Year with YEARFRAC 362

Index 363