Contents
Part I Mastering Excel formulas
Chapter 1 Building basic formulas
Understanding operator precedence
Controlling the order of precedence
Controlling worksheet calculation
Understanding relative reference format
Understanding absolute reference format
Copying a formula without adjusting relative references
Displaying all worksheet formulas
Displaying a cell’s formula by using FORMULATEXT()
Converting a formula to a value
Working with range names in formulas
Working with links in formulas
Understanding external references
Chapter 2 Creating advanced formulas
Functions that use or return arrays
Using iteration and circular references
Applying data-validation rules to cells
Using dialog box controls on a worksheet
Adding a control to a worksheet
Linking a control to a cell value
Understanding the worksheet controls
Chapter 3 Troubleshooting formulas
Understanding Excel’s error values
Avoiding #NAME? errors when deleting range names
Missing or mismatched parentheses
Handling formula errors with IFERROR()
Using the formula error checker
Part II Harnessing the power of functions
Chapter 4 Understanding functions
Typing a function into a formula
Using the Insert Function feature
Chapter 5 Working with text functions
Working with characters and codes
Displaying when a workbook was last updated
Removing unwanted characters from a string
The TEXTJOIN() function: Concatenating text with a delimiter
The REPT() function: Repeating a character or string
Converting text to sentence case
The FIND() and SEARCH() functions
Extracting a first name or last name
Extracting first name, last name, and middle initial
Substituting one substring for another
Removing a character from a string
Removing two different characters from a string
Chapter 6 Working with logical and information functions
Adding intelligence with logical functions
Performing multiple logical tests
Combining logical functions with arrays
Getting data with information functions
The SHEET() and SHEETS() functions
Chapter 7 Working with lookup functions
Taking a look at Excel’s lookup functions
Determining the name of the day of the week
Determining the month of the fiscal year
Calculating weighted questionnaire results
Integrating CHOOSE() and worksheet option buttons
Returning a customer discount rate with a range lookup
Returning a tax rate with a range lookup
The MATCH() and INDEX() functions
Chapter 8 Working with date and time functions
How Excel deals with dates and times
Calculating the difference between two dates
Calculating the difference between two times
Chapter 9 Working with math functions
Excel’s math and trig functions
Understanding Excel’s rounding functions
The ROUNDDOWN() and ROUNDUP() functions
The CEILING.MATH() and FLOOR.MATH() functions
The EVEN() and ODD() functions
The INT() and TRUNC() functions
Using rounding to prevent calculation errors
Summing only the positive or negative values in a range
A better formula for time differences
Determining whether a year is a leap year
Creating increasing random numbers with the SEQUENCE() function
Part III Building business formulas
Chapter 10 Implementing basic business formulas
Chapter 11 Building descriptive statistical formulas
Understanding descriptive statistics
The LARGE() and SMALL() functions
Performing calculations on the top k values
Performing calculations on the bottom k values
Working with rank and percentile
Calculating measures of variation
Calculating the standard deviation
Working with frequency distributions
Chapter 12 Building inferential statistical formulas
Understanding inferential statistics
Determining whether two variables are related
Working with probability distributions
Discrete probability distributions
Understanding the normal distribution and the NORM.DIST() function
Determining confidence intervals
Chapter 13 Applying regression to track trends and make forecasts
Using simple regression on linear data
Analyzing trends using best-fit lines
Using simple regression on nonlinear data
Working with an exponential trend
Working with a logarithmic trend
Using polynomial regression analysis
Using multiple regression analysis
Chapter 14 Building loan formulas
Understanding the time value of money
Calculating interest costs, part I
Calculating the principal and interest
Calculating interest costs, part II
Calculating cumulative principal and interest
Building a loan amortization schedule
Building a fixed-rate amortization schedule
Building a dynamic amortization schedule
Calculating the term of a loan
Calculating the interest rate required for a loan
Calculating how much you can borrow
Chapter 15 Working with investment formulas
Understanding compound interest
Nominal versus effective interest
Converting between the nominal rate and the effective rate
The future value of a lump sum
The future value of a series of deposits
The future value of a lump sum plus deposits
Working toward an investment goal
Calculating the required interest rate
Calculating the required number of periods
Calculating the required regular deposit
Calculating the required initial deposit
Calculating the future value with varying interest rates
Chapter 16 Building discount formulas
Calculating present value using PV()
Income investing versus purchasing a rental property
Calculating the net present value
Calculating net present value using NPV()
Net present value with varying cash flows
Net present value with nonperiodic cash flows
Calculating the payback period
Simple undiscounted payback period
Exact undiscounted payback point
Calculating the internal rate of return
Calculating the internal rate of return for nonperiodic cash flows
Calculating multiple internal rates of return
Part IV Building business models
Chapter 17 Analyzing data with tables
Sorting table data into an array, part I: The SORT() function
Sorting table data into an array, part II: The SORTBY() function
Using complex criteria to filter a table
Filtering table data with the FILTER() function
Referencing tables in formulas
Chapter 18 Analyzing data with PivotTables
Working with PivotTable subtotals
Hiding PivotTable grand totals
Customizing the subtotal calculation
Changing the value field summary calculation
Using a difference summary calculation
Toggling the difference calculation with VBA
Using a percentage summary calculation
Using a running total summary calculation
Using an index summary calculation
Creating custom PivotTable calculations
Using PivotTable results in a worksheet formula
Chapter 19 Using Excel’s business modeling tools
Setting up a one-input data table
Adding more formulas to the input table
Setting up a two-input data table
A note about Goal Seek’s approximations
Performing a break-even analysis
Setting up your worksheet for scenarios
Chapter 20 Solving complex problems with Solver
Saving a solution as a scenario
Selecting the method Solver uses