Microsoft Excel Formulas & Functions For Dummies, 5ed

Ken Bluttman

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

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

 

Just in CASE

Index