Microsoft Excel 2019 Formulas and Functions, First Edition

Table of contents

1. Cover Page
2. Title Page
3. Copyright Page
4. Dedication Page
5. Contents at a Glance
6. Contents
7. Acknowledgments
8. About the author
9. Introduction
   1. What’s in the book
   2. This book’s special features
   3. About the companion content
   4. Support and feedback
10. Part I: Mastering Excel formulas
    1. Chapter 1 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
    2. Chapter 2 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
    3. Chapter 3 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
11. Part II: Harnessing the power of functions
    1. Chapter 4 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
    2. Chapter 5 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. Searching for substrings
       7. Substituting one substring for another
    3. Chapter 6 Working with logical and information functions
       1. Adding intelligence with logical functions
       2. Getting data with information functions
    4. Chapter 7 Working with lookup functions
       1. Taking a look at Excel’s lookup functions
       2. Understanding lookup tables
       3. The CHOOSE() function
       4. Looking up values in tables
    5. Chapter 8 Working with date and time functions
       1. How Excel deals with dates and times
       2. Using Excel’s time functions
    6. Chapter 9 Working with math functions
       1. Excel’s math and trig functions
       2. Understanding Excel’s rounding functions
       3. Summing values
       4. The MOD() function
       5. Generating random numbers
12. Part III: Building business formulas
    1. Chapter 10 Implementing basic business formulas
       1. Pricing formulas
       2. Financial formulas
       3. Inventory formulas
       4. Liquidity formulas
    2. Chapter 11 Building descriptive statistical formulas
       1. Understanding descriptive statistics
       2. Counting items
       3. Calculating averages
       4. Calculating extreme values
       5. Working with rank and percentile
       6. Calculating measures of variation
       7. Working with frequency distributions
    3. Chapter 12 Building inferential statistical formulas
       1. Understanding inferential statistics
       2. Sampling data
       3. Determining whether two variables are related
       4. Working with probability distributions
       5. Determining confidence intervals
       6. Hypothesis testing
    4. Chapter 13 Applying 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. Chapter 14 Building loan formulas
       1. Understanding the time value of money
       2. Calculating a loan payment
       3. Building a loan amortization schedule
       4. Calculating the term of a loan
       5. Calculating the interest rate required for a loan
       6. Calculating how much you can borrow
    6. Chapter 15 Working with investment formulas
       1. Working with interest rates
       2. Calculating the future value
       3. Working toward an investment goal
    7. Chapter 16 Building discount formulas
       1. Calculating the present value
       2. Discounting cash flows
       3. Calculating the payback period
       4. Calculating the internal rate of return
13. Part IV: Building business models
    1. Chapter 17 Analyzing data with tables
       1. Sorting a table
       2. Filtering table data
       3. Referencing tables in formulas
       4. Excel’s table functions
    2. Chapter 18 Analyzing data with PivotTables
       1. Working with PivotTable subtotals
       2. Changing the value field summary calculation
       3. Creating custom PivotTable calculations
       4. Using PivotTable results in a worksheet formula
    3. Chapter 19 Using Excel’s business modeling tools
       1. Using what-if analysis
       2. Working with Goal Seek
       3. Working with scenarios
    4. Chapter 20 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
14. Index