Contents
PART I: MASTERING EXCEL RANGES AND FORMULAS
1 Getting the Most Out of Ranges
Advanced Range-Selection Techniques
Keyboard Range-Selection Tricks
Using the Go To Special Dialog Box
Advanced Range Copying and Pasting
Pasting Selected Cell Attributes
Combining Two Ranges Arithmetically
Applying Conditional Formatting to a Range
Creating Highlight Cells Rules
Changing the Scope to Define Sheet-Level Names
Using Worksheet Text to Define Names
Working with AutoComplete for Range Names
Pasting a List of Range Names in a Worksheet
Editing a Range Name’s Coordinates
Adjusting Range Name Coordinates Automatically
Using Names with the Intersection Operator
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
Formatting Numbers, Dates, and Times
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
Understanding Excel’s Error Values
Case Study: 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
Typing a Function into a Formula
Using the Insert Function Feature
Working with Characters and Codes
Displaying When a Workbook Was Last Updated
Removing Unwanted Characters from a String
The REPT() Function: Repeating a Character or String
Converting Text to Sentence Case
Case Study: Generating Account Numbers, Part I
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
Case Study: Generating Account Numbers, Part II
8 Working with Logical and Information Functions
Adding Intelligence with Logical Functions
Performing Multiple Logical Tests
Combining Logical Functions with Arrays
Case Study: Building an Accounts Receivable Aging Worksheet
Getting Data with Information Functions
The SHEET() and SHEETS() Functions
9 Working with 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
10 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
Case Study: Building an Employee Time Sheet
11 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
Case Study: Rounding Billable Time
Summing Only the Positive or Negative Values in a Range
A Better Formula for Time Differences
Determining Whether a Year Is a Leap Year
12 Working with Statistical Functions
Understanding Descriptive Statistics
Counting Items with the COUNT() Function
The LARGE() and SMALL() Functions
Performing Calculations on the Top k Values
Performing Calculations on the Bottom k Values
Calculating Measures of Variation
Calculating the Standard Deviation
Working with Frequency Distributions
Understanding the Normal Distribution and the NORMDIST() Function
The Shape of the Curve I: The SKEW() Function
The Shape of the Curve II: The KURT() Function
Using the Analysis ToolPak Statistical Tools
Using the Descriptive Statistics Tool
Determining the Correlation Between Data
Using the Random Number Generation Tool
Working with Rank and Percentile
PART III: BUILDING BUSINESS MODELS
Performing a More Complex Sort
Sorting a Table in Natural Order
Using Filter Lists to Filter a Table
Using Complex Criteria to Filter a Table
Copying Filtered Data to a Different Range
Referencing Tables in Formulas
Table Functions That Don’t Require a Criteria Range
Table Functions That Accept Multiple Criteria
Table Functions That Require a Criteria Range
Case Study: Applying Statistical Table Functions to a Defects Database
14 Analyzing Data with PivotTables
Building a PivotTable from a Table or Range
Building a PivotTable from an External Database
Working with and Customizing a PivotTable
Working with PivotTable Subtotals
Hiding PivotTable Grand Totals
Customizing the Subtotal Calculation
Changing the Data Field Summary Calculation
Using a Difference Summary Calculation
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
15 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
16 Using Regression to Track Trends and Make Forecasts
Using Simple Regression on Linear Data
Analyzing Trends Using Best-Fit Lines
Case Study: Trend Analysis and Forecasting for a Seasonal Sales Model
Using Simple Regression on Nonlinear Data
Working with an Exponential Trend
Working with a Logarithmic Trend
Using Polynomial Regression Analysis
Using Multiple Regression Analysis
17 Solving Complex Problems with Solver
Saving a Solution as a Scenario
Selecting the Method Solver Uses
Making Sense of Solver’s Messages
Case Study: Solving the Transportation Problem
PART IV: BUILDING FINANCIAL FORMULAS
Understanding the Time Value of Money
Calculating Interest Costs, Part 1
Calculating the Principal and Interest
Calculating Interest Costs, Part 2
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
Case Study: Working with Mortgages
19 Building 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
Case Study: Building an Investment Schedule
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