# Excel Formulas and Functions For Dummies [NOOK Book]

( 5 )

### Overview

Put the power of Excel formulas and functions to work for you!

Excel is a complex program. Mastering the use of formulas and functions lets you use Excel to compute useful day-to-day information, such as calculating the true cost of credit card purchases or comparing 15-year and 30-year mortgage costs. This fun and friendly book demystifies Excel's built-in functions so you can put them to work.

You'll find step-by-step instructions on 150 of ...

See more details below

#### Available on NOOK devices and apps NOOK Devices NOOK HD/HD+ Tablet NOOK NOOK Color NOOK Tablet Tablet/Phone NOOK for Windows 8 Tablet NOOK for iOS NOOK for Android NOOK Kids for iPad PC/Mac NOOK for Windows 8 NOOK for PC NOOK for Mac NOOK Study NOOK for Web

Want a NOOK? Explore Now

NOOK Book (eBook)
\$13.99
BN.com price
(Save 44%)\$24.99 List Price
Marketplace
BN.com

## All Available Formats & Editions

• ——
NOOK Book (2)
• Paperback (3)

### Overview

Put the power of Excel formulas and functions to work for you!

Excel is a complex program. Mastering the use of formulas and functions lets you use Excel to compute useful day-to-day information, such as calculating the true cost of credit card purchases or comparing 15-year and 30-year mortgage costs. This fun and friendly book demystifies Excel's built-in functions so you can put them to work.

You'll find step-by-step instructions on 150 of Excel's most useful functions, how they work within formulas, and how to use them to make your life easier.

• See how to use 150 of Excel's most useful functions, with real-world examples showing how each function is used within a formula
• Learn to calculate the costs of leasing versus buying a car, compute classroom grades, create an amortization table, or evaluate investment performance
• Fully updated for Excel 2010, but the principles will work with earlier versions of Excel as well
• Includes essential coverage of an additional 85 functions

In the ever-popular, non-threatening For Dummies style, Excel Formulas and Functions For Dummies, 2nd Edition makes Excel's power accessible to you.

### Product Details

• ISBN-13: 9781118460856
• Publisher: Wiley, John & Sons, Incorporated
• Publication date: 2/8/2013
• Sold by: Barnes & Noble
• Format: eBook
• Edition number: 3
• Pages: 408
• Sales rank: 188,185
• File size: 14 MB

### Meet the Author

Ken Bluttman has been a software developer for 20 years. He is the author of several books, including Excel Charts For Dummies.

Peter Aitken has written about computers and programming for over 10 years and has written more than 35 books, including Excel Programming Weekend Crash Course.

Introduction 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

Getting help 18

Gaining the Upper Hand on Formulas 19

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

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

Chapter 7: Using Basic Math Functions 119

Adding It All Together with the SUM Function 119

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

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

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

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

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 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

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

## Customer Reviews

Average Rating 3
( 5 )
Rating Distribution

(1)

(1)

(0)

(2)

### 1 Star

(1)

Your Name: Create a Pen Name or

### Barnes & Noble.com Review Rules

Our reader reviews allow you to share your comments on titles you liked, or didn't, with others. By submitting an online review, you are representing to Barnes & Noble.com that all information contained in your review is original and accurate in all respects, and that the submission of such content by you and the posting of such content by Barnes & Noble.com does not and will not violate the rights of any third party. Please follow the rules below to help ensure that your review can be posted.

### Reviews by Our Customers Under the Age of 13

We highly value and respect everyone's opinion concerning the titles we offer. However, we cannot allow persons under the age of 13 to have accounts at BN.com or to post customer reviews. Please see our Terms of Use for more details.

### What to exclude from your review:

Please do not write about reviews, commentary, or information posted on the product page. If you see any errors in the information on the product page, please send us an email.

### Reviews should not contain any of the following:

• - HTML tags, profanity, obscenities, vulgarities, or comments that defame anyone
• - Time-sensitive information such as tour dates, signings, lectures, etc.
• - Single-word reviews. Other people will read your review to discover why you liked or didn't like the title. Be descriptive.
• - Comments focusing on the author or that may ruin the ending for others
• - Phone numbers, addresses, URLs
• - Pricing and availability information or alternative ordering information

### Reminder:

• - By submitting a review, you grant to Barnes & Noble.com and its sublicensees the royalty-free, perpetual, irrevocable right and license to use the review in accordance with the Barnes & Noble.com Terms of Use.
• - Barnes & Noble.com reserves the right not to post any review -- particularly those that do not follow the terms and conditions of these Rules. Barnes & Noble.com also reserves the right to remove any review at any time without notice.
Search for Products You'd Like to Recommend

### Recommend other products that relate to your review. Just search for them below and share!

Create a Pen Name

Your Pen Name is your unique identity on BN.com. It will appear on the reviews you write and other website activities. Your Pen Name cannot be edited, changed or deleted once submitted.

Your Pen Name can be any combination of alphanumeric characters (plus - and _), and must be at least two characters long.

Continue Anonymously
Sort by: Showing all of 5 Customer Reviews
• Anonymous

Posted January 12, 2010

#### Just what I needed

I needed a book just for formulas. I've looked for 3 different ones so far and it had each one. I still need to take the time to look at others to see if they would be helpful.

1 out of 1 people found this review helpful.

• Posted July 8, 2011

#### Cool Intro to Arcane Subject

Read and apply the easy-to-use examples and explanations in this book to break into an intermediate level of knowledge in Excel -- which is more than the vast majority of people ever can demonstrate. You will be able to use Excel for truly useful work in your office and never be embarrassed or have to say "no" when asked to do many of the things your boss or workgroup expects you to be able to do when dealing with numeric or textual data

This book has been one of the best purchases I have ever made in my beginner's techical library.

• Anonymous

Posted November 3, 2012

No text was provided for this review.

• Anonymous

Posted January 4, 2011

No text was provided for this review.

• Anonymous

Posted November 27, 2010

No text was provided for this review.

Sort by: Showing all of 5 Customer Reviews

If you find inappropriate content, please report it to Barnes & Noble
Why is this product inappropriate?