Excel Formulas and Functions For Dummies, 4th Edition

Excel Formulas and Functions For Dummies, 4th Edition

by Ken Bluttman
Released December 2015
Publisher(s): For Dummies
ISBN: 9781119076780

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Grab these Excel formulas and functions to make your life easier!

Are you intimidated by major financial choices, like which loan to get or how to grow your savings? Don't worry—we all are! But Excel Formulas & Functions For Dummies, 4th Edition can take some of the pain out of the data organization and analysis processes. This step-by-step reference sheds light on Microsoft Excel's 150 most useful functions, and offers detailed instructions on how to implement them. Additionally, each function is illustrated by helpful, real-world examples that show how they are used within a larger formula. To take your knowledge of Excel's functions a step further, 85 specialized functions are described in abbreviated form so you can use Excel to better support your decision-making process when securing a mortgage, buying a car, computing classroom grades, evaluating investment performance, and more.

Functions are predefined formulas that you can use to make data analysis a bit easier within the Microsoft Excel framework. Functions use specific values, called arguments, to calculate a variety of things, from simple sums and averages to more complicated loan payments.

  • Explore the 150 most useful functions that help Microsoft Excel make your life easier
  • Access real-world examples of how functions fit into larger formulas
  • Discover 85 specialized functions, which are described in abbreviated form and take your knowledge of Excel to the next level
  • Understand how Microsoft Excel can help you make key decisions, such as whether to go with a 15-year or 30-year mortgage

Excel Formulas & Functions For Dummies, 4th Edition brings order to chaotic data—and helps you make decisions with confidence!

Table of contents

    1. Cover
    2. Introduction
      1. About This Book
      2. Foolish Assumptions
      3. How to Use This Book
      4. Icons Used in This Book
      5. Beyond the Book
      6. Where to Go from Here
    3. Part I: Getting Started with Formulas and Functions
      1. Chapter 1: Tapping Into Formula and Function Fundamentals
        1. Working with Excel Fundamentals
        2. Gaining the Upper Hand on Formulas
        3. Using Functions in Formulas
      2. Chapter 2: Saving Time with Function Tools
        1. Getting Familiar with the Insert Function Dialog Box
        2. Finding the Correct Function
        3. Entering Functions Using the Insert Function Dialog Box
        4. Directly Entering Formulas and Functions
      3. Chapter 3: Saying “Array!” for Formulas and Functions
        1. Discovering Arrays
        2. Using Arrays in Formulas
        3. Working with Functions That Return Arrays
      4. Chapter 4: Fixing Formula Boo-Boos
        1. Catching Errors As You Enter Them
        2. Auditing Formulas
        3. Watching the Watch Window
        4. Evaluating and Checking Errors
        5. Making an Error Behave the Way You Want
    4. Part II: Doing the Math
      1. Chapter 5: Calculating Loan Payments and Interest Rates
        1. Understanding How Excel Handles Money
        2. Figuring Loan Calculations
      2. Chapter 6: Appreciating What You’ll Get, Depreciating What You’ve Got
        1. Looking into the Future
        2. Depreciating the Finer Things in Life
        3. Measuring Your Internals
      3. Chapter 7: Using Basic Math Functions
        1. Adding It All Together with the SUM Function
        2. Rounding Out Your Knowledge
        3. Leaving All Decimals Behind with INT
        4. Leaving Some Decimals Behind with TRUNC
        5. Looking for a Sign
        6. Ignoring Signs
      4. Chapter 8: Advancing Your Math
        1. Using PI to Calculate Circumference and Diameter
        2. Generating and Using Random Numbers
        3. Ordering Items
        4. Combining
        5. Raising Numbers to New Heights
        6. Multiplying Multiple Numbers
        7. Using What Remains with the MOD Function
        8. Summing Things Up
        9. Getting an Angle on Trigonometry
    5. Part III: Solving with Statistics
      1. Chapter 9: Throwing Statistics a Curve
        1. Getting Stuck in the Middle with AVERAGE, MEDIAN, and MODE
        2. Deviating from the Middle
        3. Analyzing Data with Percentiles and Bins
        4. Going for the Count
      2. Chapter 10: Using Significance Tests
        1. Testing to the T
        2. Comparing Results with an Estimate
      3. Chapter 11: Rolling the Dice on Predictions and Probability
        1. Modeling
        2. Getting It Straight: Using SLOPE and INTERCEPT to Describe Linear Data
        3. What’s in the Future: Using FORECAST, TREND, and GROWTH to Make Predictions
        4. Using NORM.DIST and POISSON.DIST to Determine Probabilities
    6. Part IV: Dancing with Data
      1. Chapter 12: Dressing Up for Date Functions
        1. Understanding How Excel Handles Dates
        2. Formatting Dates
        3. Making a Date with DATE
        4. Breaking a Date with DAY, MONTH, and YEAR
        5. Converting a Date from Text
        6. Finding Out What TODAY Is
        7. Determining the Day of the Week
        8. Working with Workdays
        9. Calculating Time between Two Dates with the DATEDIF Function
      2. Chapter 13: Keeping Well-Timed Functions
        1. Understanding How Excel Handles Time
        2. Formatting Time
        3. Keeping TIME
        4. Converting Text to Time with TIMEVALUE
        5. Deconstructing Time with HOUR, MINUTE, and SECOND
        6. Finding the Time NOW
        7. Calculating Elapsed Time over Days
      3. Chapter 14: Using Lookup, Logical, and Reference Functions
        1. Testing on One Condition
        2. Choosing the Right Value
        3. Let's Be Logical
        4. Finding Where It Is
        5. Looking It Up
      4. Chapter 15: Digging Up the Facts
        1. Getting Informed with the CELL Function
        2. Getting Information about Excel and Your Computer System
        3. Finding What IS and What IS Not
        4. Getting to Know Your Type
      5. Chapter 16: Writing Home about Text Functions
        1. Breaking Apart Text
        2. Putting Text Together with CONCATENATE
        3. Changing Text
        4. Comparing, Finding, and Measuring Text
      6. Chapter 17: Playing Records with Database Functions
        1. Putting Your Data into a Database Structure
        2. Working with Database Functions
        3. Fine-Tuning Criteria with AND and OR
        4. Adding Only What Matters with DSUM
        5. Going for the Middle with DAVERAGE
        6. Counting Only What Matters with DCOUNT
        7. Finding Highest and Lowest with DMIN and DMAX
        8. Finding Duplicate Values with DGET
        9. Being Productive with DPRODUCT
    7. Part V: The Part of Tens
      1. Chapter 18: Ten T ips for Working with Formulas
        1. Master Operator Precedence
        2. Display Formulas
        3. Fix Formulas
        4. Use Absolute References
        5. Turn Calc On/Turn Calc Off
        6. Use Named Areas
        7. Use Formula Auditing
        8. Use Conditional Formatting
        9. Use Data Validation
        10. Create Your Own Functions
      2. Chapter 19: Ten Functions You Really Should Know
        1. SUM
        2. AVERAGE
        3. COUNT
        4. INT and ROUND
        5. IF
        6. NOW and TODAY
        7. HLOOKUP and VLOOKUP
        8. ISNUMBER
        9. MIN and MAX
        10. SUMIF and COUNTIF
      3. Chapter 20: Some Really Cool Functions
        1. Work with Hexadecimal, Octal, Decimal, and Binary Numbers
        2. Convert Units of Measurement
        3. Find the Greatest Common Divisor and the Least Common Multiple
        4. Easily Generate a Random Number
        5. Convert to Roman Numerals
        6. Factor in a Factorial
        7. Determine Part of a Year with YEARFRAC
        8. Find the Data TYPE
    8. About the Author
    9. Cheat Sheet
    10. Advertisement Page
    11. Connect with Dummies
    12. End User License Agreement

Product information

  • Title: Excel Formulas and Functions For Dummies, 4th Edition
  • Author(s): Ken Bluttman
  • Release date: December 2015
  • Publisher(s): For Dummies
  • ISBN: 9781119076780