# Microsoft Excel Formulas & Functions For Dummies, 5ed

ISBN: 9789390466733

INR 799

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

Introduction

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
• Getting help
• Gaining the Upper Hand on Formulas
• 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
• 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

Chapter 7: Using Basic Math Functions

• Adding It All Together with the SUM Function
• 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

• 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

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

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

Just in CASE

Index

• Name:
• Designation:
• Name of Institute:
• Email:
• * Request from personal id will not be entertained
• Moblie:
• ISBN / Title:
• ISBN:    * Please specify ISBN / Title Name clearly