Excel® 2013 Formulas and Functions

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