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.

Autorentext

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.

Klappentext
Become highly functional at work or at home

Excel® spreadsheets let you put stuff in boxesand then do cool stuff with that stuff! Excel Formulas & Functions For Dummies shows you just how cool it can get, introducing the basics of Excel calculations and taking you all the way through statistics, data analysis, and lots more fanciness. Even if you're already pretty fancy when it comes to spreadsheets, you'll want to have this book handy so you can find that one function you're always forgetting. When you're done crunching the numbers, this book will even help you make it look pretty. Excellent! Inside

• Excel's most powerful tools
• Arrays and money math
• Common financial calculations
• Basic and advanced statistics
• Date and time issues
• Lookups and logic functions
• Analysis of text-based data
• All the latest new features

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