You are previewing Excel® 2013 Formulas and Functions.
O'Reilly logo
Excel® 2013 Formulas and Functions

Book Description

Master core Excel 2013 tools for building powerful, reliable spreadsheets!

Excel expert Paul McFedries shows how to use Excel 2013’s core features to solve problems and get the answers you need. Using real-world examples, McFedries helps you get the absolute most out of features and improvements ranging from FlashFill to Excel’s newest functions. Along the way, you discover the fastest, best ways to handle essential day-to-day tasks ranging from generating account numbers to projecting the impact of inflation.

Becoming an Excel expert has never been easier! You’ll find crystal-clear instructions; insider insights; even complete step-by-step projects for building timesheets, projecting cash flow, aging receivables, analyzing defects, and more..

•   Quickly create powerful spreadsheets with FlashFill

•   Use conditional formatting to instantly reveal anomalies, problems, or opportunities

•   Analyze your data with standard tables and PivotTables

•   Use complex criteria to filter data in lists

•   Understand correlations between data

•   Perform sophisticated what-if analyses

•   Use regression to track trends and make forecasts

•   Build loan, investment, and discount formulas

•   Validate data, troubleshoot problems, and build more accurate, trustworthy spreadsheets

About MrExcel Library:  Every book in the MrExcel Library pinpoints a specific set of crucial Excel tasks and presents focused skills and examples for performing them rapidly and effectively. Selected by Bill Jelen, Microsoft Excel MVP and mastermind behind the leading Excel solutions website MrExcel.com, these books will

•   Dramatically increase your productivity—saving you 50 hours a year or more

•   Present proven, creative strategies for solving real-world problems

•   Show you how to get great results, no matter how much data you have

•   Help you avoid critical mistakes that even experienced users make

CATEGORY: Spreadsheets

COVERS: Microsoft Office Excel 2013

Table of Contents

  1. Title Page
  2. Copyright Page
  3. Contents at a Glance
  4. Contents
  5. About the Author
  6. Dedication
  7. Acknowledgments
  8. We Want to Hear from You!
  9. Reader Services
  10. Introduction
    1. What’s in the Book
    2. This Book’s Special Features
  11. Part I. Mastering Excel Ranges and Formulas
    1. 1. Getting the Most Out of Ranges
      1. Advanced Range-Selection Techniques
      2. Data Entry in a Range
      3. Filling a Range
      4. Creating a Series
      5. Advanced Range Copying
      6. Clearing a Range
      7. Applying Conditional Formatting to a Range
      8. From Here
    2. 2. Using Range Names
      1. Defining a Range Name
      2. Working with Range Names
      3. From Here
    3. 3. Building Basic Formulas
      1. Understanding Formula Basics
      2. Understanding Operator Precedence
      3. Controlling Worksheet Calculation
      4. Copying and Moving Formulas
      5. Displaying Worksheet Formulas
      6. Converting a Formula to a Value
      7. Working with Range Names in Formulas
      8. Working with Links in Formulas
      9. Formatting Numbers, Dates, and Times
      10. From Here
    4. 4. Creating Advanced Formulas
      1. Working with Arrays
      2. Using Iteration and Circular References
      3. Consolidating Multisheet Data
      4. Applying Data-Validation Rules to Cells
      5. Using Dialog Box Controls on a Worksheet
      6. From Here
    5. 5. Troubleshooting Formulas
      1. Understanding Excel’s Error Values
      2. Fixing Other Formula Errors
      3. Handling Formula Errors with IFERROR()
      4. Using the Formula Error Checker
      5. Auditing a Worksheet
      6. From Here
  12. Part II. Harnessing the Power of Functions
    1. 6. Understanding Functions
      1. About Excel’s Functions
      2. The Structure of a Function
      3. Typing a Function into a Formula
      4. Using the Insert Function Feature
      5. Loading the Analysis ToolPak
      6. From Here
    2. 7. Working with Text Functions
      1. Excel’s Text Functions
      2. Working with Characters and Codes
      3. Converting Text
      4. Formatting Text
      5. Manipulating Text
      6. Removing Unwanted Characters from a String
      7. Extracting a Substring
      8. Searching for Substrings
      9. Substituting One Substring for Another
      10. From Here
    3. 8. Working with Logical and Information Functions
      1. Adding Intelligence with Logical Functions
      2. Getting Data with Information Functions
      3. From Here
    4. 9. Working with Lookup Functions
      1. Understanding Lookup Tables
      2. The CHOOSE() Function
      3. Looking Up Values in Tables
      4. From Here
    5. 10. Working with Date and Time Functions
      1. How Excel Deals with Dates and Times
      2. Using Excel’s Date Functions
      3. Using Excel’s Time Functions
      4. From Here
    6. 11. Working with Math Functions
      1. Understanding Excel’s Rounding Functions
      2. Summing Values
      3. The MOD() Function
      4. Generating Random Numbers
      5. From Here
    7. 12. Working with Statistical Functions
      1. Understanding Descriptive Statistics
      2. Counting Items with the COUNT() Function
      3. Calculating Averages
      4. Calculating Extreme Values
      5. Calculating Measures of Variation
      6. Working with Frequency Distributions
      7. Using the Analysis ToolPak Statistical Tools
      8. From Here
  13. Part III. Building Business Models
    1. 13. Analyzing Data with Tables
      1. Converting a Range to a Table
      2. Basic Table Operations
      3. Sorting a Table
      4. Filtering Table Data
      5. Referencing Tables in Formulas
      6. Excel’s Table Functions
      7. From Here
    2. 14. Analyzing Data with PivotTables
      1. What Are PivotTables?
      2. Building PivotTables
      3. Working with PivotTable Subtotals
      4. Changing the Data Field Summary Calculation
      5. Creating Custom PivotTable Calculations
      6. From Here
    3. 15. Using Excel’s Business-Modeling Tools
      1. Using What-If Analysis
      2. Working with Goal Seek
      3. Working with Scenarios
      4. From Here
    4. 16. Using Regression to Track Trends and Make Forecasts
      1. Choosing a Regression Method
      2. Using Simple Regression on Linear Data
      3. Using Simple Regression on Nonlinear Data
      4. Using Multiple Regression Analysis
      5. From Here
    5. 17. Solving Complex Problems with Solver
      1. Some Background on Solver
      2. Loading Solver
      3. Using Solver
      4. Adding Constraints
      5. Saving a Solution as a Scenario
      6. Setting Other Solver Options
      7. Making Sense of Solver’s Messages
      8. Displaying Solver’s Reports
      9. From Here
  14. Part IV. Building Financial Formulas
    1. 18. Building Loan Formulas
      1. Understanding the Time Value of Money
      2. Calculating the Loan Payment
      3. Building a Loan Amortization Schedule
      4. Calculating the Term of the Loan
      5. Calculating the Interest Rate Required for a Loan
      6. Calculating How Much You Can Borrow
      7. From Here
    2. 19. Building Investment Formulas
      1. Working with Interest Rates
      2. Calculating the Future Value
      3. Working Toward an Investment Goal
      4. From Here
    3. 20. Building Discount Formulas
      1. Calculating the Present Value
      2. Discounting Cash Flows
      3. Calculating the Payback Period
      4. Calculating the Internal Rate of Return
      5. From Here
  15. Index