Microsoft excel formulas and functions for dummies

Introduction

About This Book

Foolish Assumptions

How to Use This Book

Icons Used in This Book

Where to Go from Here



Part 1: Getting Started with Formulas and Functions

Chapter 1: Tapping Into Formula and Function Fundamentals

Working with Excel Fundamentals
Understanding workbooks and worksheets
Introducing the Formulas Tab
Working with rows, column, cells, ranges, and tables
Formatting your data
Getting help
Gaining the Upper Hand on Formulas
Entering your first formula
Understanding references
Copying formulas with the fill handle
Assembling formulas the right way
Using Functions in Formulas
Looking at what goes into a function
Arguing with a function
Nesting functions


Chapter 2: Saving Time with Function Tools

Getting Familiar with the Insert Function Dialog Box
Finding the Correct Function
Entering Functions Using the Insert Function Dialog Box
Selecting a function that takes no arguments
Selecting a function that uses arguments
Entering cells, ranges, named areas, and tables as function arguments
Getting help in the Insert Function dialog box
Using the Function Arguments dialog box to edit functions
Directly Entering Formulas and Functions
Entering formulas and functions in the Formula Bar
Entering formulas and functions directly in worksheet cells


Chapter 3: Saying "Array!" for Formulas and Functions

Discovering Arrays
Using Arrays in Formulas
Working with Functions That Return Arrays


Chapter 4: Fixing Formula Boo-Boos

Catching Errors As You Enter Them
Getting parentheses to match
Avoiding circular references
Mending broken links
Using the Formula Error Checker
Auditing Formulas
Watching the Watch Window
Evaluating and Checking Errors
Making an Error Behave the Way You Want


Part 2: Doing the Math

Chapter 5: Calculating Loan Payments and Interest Rates

Understanding How Excel Handles Money
Going with the cash flow
Formatting for currency
Choosing separators
Figuring Loan Calculations
Calculating the payment amount
Calculating interest payments
Calculating payments toward principal
Calculating the number of payments
Calculating the number of payments with PDURATION
Calculating the interest rate
Calculating the principal


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

Looking into the Future
Depreciating the Finer Things in Life
Calculating straight-line depreciation
Creating an accelerated depreciation schedule
Creating an even faster accelerated depreciation schedule
Calculating a midyear depreciation schedule
Measuring Your Internals


Chapter 7: Using Basic Math Functions

Adding It All Together with the SUM Function
Rounding Out Your Knowledge
Just plain old rounding
Rounding in one direction
Leaving All Decimals Behind with INT
Leaving Some Decimals Behind with TRUNC
Looking for a Sign
Ignoring Signs


Chapter 8: Advancing Your Math

Using PI to Calculate Circumference and Diameter
Generating and Using Random Numbers
The all-purpose RAND function
Precise randomness with RANDBETWEEN
Ordering Items
Combining
Raising Numbers to New Heights
Multiplying Multiple Numbers
Using What Remains with the MOD Function
Summing Things Up
Using SUBTOTAL
Using SUMPRODUCT
Using SUMIF and SUMIFS
Getting an Angle on Trigonometry
Three basic trigonometry functions
Degrees and radians


Part 3: Solving with Statistics

Chapter 9: Throwing Statistics a Curve

Getting Stuck in the Middle with AVERAGE, MEDIAN, and MODE
Deviating from the Middle
Measuring variance
Analyzing deviations
Looking for normal distribution
Skewing from the norm
Comparing data sets
Analyzing Data with Percentiles and Bins
QUARTILE.INC and QUARTILE.EXC
PERCENTILE.INC and PERCENTILE.EXC
RANK
PERCENTRANK
FREQUENCY
MIN and MAX
LARGE and SMALL
Going for the Count
COUNT and COUNTA
COUNTIF


Chapter 10: Using Significance Tests

Testing to the T
Comparing Results with an Estimate


Chapter 11: Rolling the Dice on Predictions and Probability

Modeling
Linear model
Exponential model
Getting It Straight: Using SLOPE and INTERCEPT to Describe Linear Data
What's Ahead: Using FORECAST, TREND, and GROWTH to Make Predictions
FORECAST
TREND
GROWTH
Using NORM.DIST and POISSON.DIST to Determine Probabilities
NORM.DIST
POISSON.DIST


Part 4: Dancing with Data

Chapter 12: Dressing Up for Date Functions

Understanding How Excel Handles Dates
Formatting Dates
Making a Date with DATE
Breaking a Date with DAY, MONTH, and YEAR
Isolating the day
Isolating the month
Isolating the year
Converting a Date from Text
Finding Out What TODAY Is
Counting the days until your birthday
Counting your age in days
Determining the Day of the Week
Working with Workdays
Determining workdays in a range of dates
Workdays in the future
Calculating Time Between Two Dates with the DATEDIF Function


Chapter 13: Keeping Well-Timed Functions

Understanding How Excel Handles Time
Formatting Time
Keeping TIME
Converting Text to Time with TIMEVALUE
Deconstructing Time with HOUR, MINUTE, and SECOND
Isolating the hour
Isolating the minute
Isolating the second
Finding the Time NOW
Calculating Elapsed Time Over Days


Chapter 14: Using Lookup, Logical, and Reference Functions

Testing on One Condition
Choosing the Right Value
Let's Be Logical
NOT
AND and OR
XOR
Finding Where the Data Is
ADDRESS
ROW, ROWS, COLUMN, and COLUMNS
OFFSET
Looking It Up
HLOOKUP and VLOOKUP
MATCH and INDEX
FORMULATEXT
NUMBERVALUE


Chapter 15: Digging Up the Facts

Getting Informed with the CELL Function
Getting Information About Excel and Your Computer System
Finding What IS and What IS Not
ISERR, ISNA, and ISERROR
ISBLANK, ISNONTEXT, ISTEXT, and ISNUMBER
Getting to Know Your Type


Chapter 16: Writing Home About Text Functions

Breaking Apart Text
Bearing to the LEFT
Swinging to the RIGHT
Staying in the MIDdle
Finding the long of it with LEN
Putting Text Together with CONCATENATE
Changing Text
Making money
Turning numbers into text
Repeating text
Swapping text
Giving text a trim
Making a case
Comparing, Finding, and Measuring Text
Going for perfection with EXACT
Finding and searching


Chapter 17: Playing Records with Database Functions

Putting Your Data into a Database Structure
Working with Database Functions
Establishing your database
Establishing the criteria area
Fine-Tuning Criteria with AND and OR
Adding Only What Matters with DSUM
Going for the Middle with DAVERAGE
Counting Only What Matters with DCOUNT
Finding Highest and Lowest with DMIN and DMAX
Finding Duplicate Values with DGET
Being Productive with DPRODUCT


Part 5: The Part of Tens

Chapter 18: Ten Tips for Working with Formulas

Master Operator Precedence
Display Formulas
Fix Formulas
Use Absolute References
Turn Calc On/Turn Calc Off
Use Named Areas
Use Formula Auditing
Use Conditional Formatting
Use Data Validation
Create Your Own Functions


Chapter 19: Ten Functions You Really Should Know

SUM
AVERAGE
COUNT
INT and ROUND
INT
ROUND
IF
NOW and TODAY
HLOOKUP and VLOOKUP
ISNUMBER
MIN and MAX
SUMIF and COUNTIF


Chapter 20: Ten Really Cool Functions

Work with Hexadecimal, Octal, Decimal, and Binary Numbers
Convert Units of Measurement
Find the Greatest Common Divisor and the Least Common Multiple
Easily Generate a Random Number
Convert to Roman Numerals
Factor in a Factorial
Determine Part of a Year with YEARFRAC
Find the Data TYPE
Find the LENgth of Your Text

Description
There are more than 400 built-in functions in Excel X, from AGGREGATE to Z.TEST. The question is which ones will make my work easier? How and why should I use a particular function in my formulas? Excel Formulas and Functions For Dummies, 5th Edition is a thorough but easy-to-read coverage of powerful Excel functions. This book gives detailed step-by-step instruction on Excel's 150 most useful functions. The use of each function will be illustrated by helpful, real-world examples showing how a function is used within a formula. Another 85 specialized functions will be described in abbreviated form.