Excel 2013 Formulas and Functions

Paul McFedries is an Excel expert and full-time technical writer. Paul has been authoring computer books since 1991 and has more than 80 books to his credit, which combined have sold more than four million copies worldwide. His titles include the Que Publishing books Windows 8 In Depth (with coauthor Brian Knittel), PCs for Grownups, and Tweak It and Freak It: A Killer Guide to Making Windows Run Your Way, as well as the Sams Publishing book Windows 7 Unleashed. Paul is also the proprietor of Word Spy (www.wordspy.com), a website devoted to lexpionage, the sleuthing of new words and phrases that have entered the English language. Please drop by Paul’s personal website at www.mcfedries.com or follow Paul on Twitter at twitter.com/paulmcf and twitter.com/wordspy.

Introduction

Part I: Mastering Excel Ranges and Formulas

Chapter 1 Getting the Most Out of Ranges

Advanced Range-Selection Techniques

Mouse Range-Selection Tricks

Keyboard Range-Selection Tricks

Working with 3D Ranges

Selecting a Range Using Go To

Using the Go To Special Dialog Box

Data Entry in a Range

Filling a Range

Using the Fill Handle

Flash-Filling a Range

Creating a Series

Advanced Range Copying

Copying Selected Cell Attributes

Combining Two Ranges Arithmetically

Transposing Rows and Columns

Clearing a Range

Applying Conditional Formatting to a Range

Creating Highlight Cells Rules

Creating Top/Bottom Rules

Adding Data Bars

Adding Color Scales

Adding Icon Sets

From Here

Chapter 2 Using Range Names

Defining a Range Name

Working with the Name Box

Using the New Name Dialog Box

Changing the Scope to Define Sheet-Level Names

Using Worksheet Text to Define Names

Naming Constants

Working with Range Names

Referring to a Range Name

Working with Name AutoComplete

Navigating Using Range Names

Pasting a List of Range Names in a Worksheet

Displaying the Name Manager

Filtering Names

Editing a Range Name’s Coordinates

Adjusting Range Name Coordinates Automatically

Changing a Range Name

Deleting a Range Name

Using Names with the Intersection Operator

From Here

Chapter 3 Building Basic Formulas

Understanding Formula Basics

Formula Limits in Excel 2013

Entering and Editing Formulas

Using Arithmetic Formulas

Using Comparison Formulas

Using Text Formulas

Using Reference Formulas

Understanding Operator Precedence

The Order of Precedence

Controlling the Order of Precedence

Controlling Worksheet Calculation

Copying and Moving Formulas

Understanding Relative Reference Format

Understanding Absolute Reference Format

Copying a Formula Without Adjusting Relative References

Displaying Worksheet Formulas

Displaying All Worksheet Formulas

Displaying a Cell’s Formula Using FORMULATEXT()

Converting a Formula to a Value

Working with Range Names in Formulas

Pasting a Name into a Formula

Applying Names to Formulas

Naming Formulas

Working with Links in Formulas

Understanding External References

Updating Links

Changing the Link Source

Formatting Numbers, Dates, and Times

Numeric Display Formats

Date and Time Display Formats

Deleting Custom Formats

From Here

Chapter 4 Creating Advanced Formulas

Working with Arrays

Using Array Formulas

Using Array Constants

Functions That Use or Return Arrays

Using Iteration and Circular References

Consolidating Multisheet Data

Consolidating by Position

Consolidating by Category

Applying Data-Validation Rules to Cells

Using Dialog Box Controls on a Worksheet

Displaying the Developer Tab

Using the Form Controls

Adding a Control to a Worksheet

Linking a Control to a Cell Value

Understanding the Worksheet Controls

From Here

Chapter 5 Troubleshooting Formulas

Understanding Excel’s Error Values

#DIV/0!

#N/A

#NAME?

Case Study: Avoiding #NAME? Errors When Deleting Range Names

#NULL!

#NUM!

#REF!

#VALUE!

Fixing Other Formula Errors

Missing or Mismatched Parentheses

Erroneous Formula Results

Fixing Circular References

Handling Formula Errors with IFERROR()

Using the Formula Error Checker

Choosing an Error Action

Setting Error Checker Options

Auditing a Worksheet

Understanding Auditing

Tracing Cell Precedents

Tracing Cell Dependents

Tracing Cell Errors

Removing Tracer Arrows

Evaluating Formulas

Watching Cell Values

From Here

Part II: Harnessing the Power of Functions

Chapter 6 Understanding Functions

About Excel’s Functions

The Structure of a Function

Typing a Function into a Formula

Using the Insert Function Feature

Loading the Analysis ToolPak

From Here

Chapter 7 Working with Text Functions

Excel’s Text Functions

Working with Characters and Codes

The CHAR() Function

The CODE() Function

Converting Text

The LOWER() Function

The UPPER() Function

The PROPER() Function

The DECIMAL() Function

The NUMBERVALUE() Function

Formatting Text

The DOLLAR() Function

The FIXED() Function

The TEXT() Function

Displaying When a Workbook Was Last Updated

Manipulating Text

Removing Unwanted Characters from a String

The TRIM() Function

The CLEAN() Function

The REPT() Function: Repeating a Character

Padding a Cell

Building Text Charts

Extracting a Substring

The LEFT() Function

The RIGHT() Function

The MID() Function

Converting Text to Sentence Case

A Date-Conversion Formula

Case Study: Generating Account Numbers

Searching for Substrings

The FIND() and SEARCH() Functions

Extracting a First Name or Last Name

Extracting First Name, Last Name, and Middle Initial

Determining the Column Letter

Substituting One Substring for Another

The REPLACE() Function

The SUBSTITUTE() Function

Removing a Character from a String

Removing Two Different Characters from a String

Case Study: Generating Account Numbers, Part II

Removing Line Feeds

From Here

Chapter 8 Working with Logical and Information Functions

Adding Intelligence with Logical Functions

Using the IF() Function

Performing Multiple Logical Tests

Combining Logical Functions with Arrays

Case Study: Building an Accounts Receivable Aging Worksheet

Calculating a Smarter Due Date

Aging Overdue Invoices

Getting Data with Information Functions

The CELL() Function

The ERROR.TYPE() Function

The INFO() Function

The SHEET() and SHEETS() Functions

The IS Functions

From Here

Chapter 9 Working with Lookup Functions

Understanding Lookup Tables

The CHOOSE() Function

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

Looking Up Values in Tables

The VLOOKUP() Function

The HLOOKUP() Function

Returning a Customer Discount Rate with a Range Lookup

Returning a Tax Rate with a Range Lookup

Finding Exact Matches

Advanced Lookup Operations

From Here

Chapter 10 Working with Date and Time Functions

How Excel Deals with Dates and Times

Entering Dates and Times

Excel and Two-Digit Years

Using Excel’s Date Functions

Returning a Date

Returning Parts of a Date

Calculating the Difference Between Two Dates

Using Excel’s Time Functions

Returning a Time

Returning Parts of a Time

Calculating the Difference Between Two Times

Case Study: Building an Employee Time Sheet

Entering the Time Sheet Data

    Calculating the Daily Hours Worked

Calculating the Weekly Hours Worked

Calculating the Weekly Pay

From Here

Chapter 11 Working with Math Functions

Understanding Excel’s Rounding Functions

The ROUND() Function

The MROUND() Function

The ROUNDDOWN() and ROUNDUP() Functions

The CEILING.MATH() and FLOOR.MATH() Functions

Determining the Fiscal Quarter in Which a Date Falls

Calculating Easter Dates

The EVEN() and ODD() Functions

The INT() and TRUNC() Functions

Using Rounding to Prevent Calculation Errors

Setting Price Points

Case Study: Rounding Billable Time

Summing Values

The SUM() Function

Calculating Cumulative Totals

Summing Only the Positive or Negative Values in a Range

The MOD() Function

A Better Formula for Time Differences

Summing Every nth Row

Determining Whether a Year Is a Leap Year

Creating Ledger Shading

Generating Random Numbers

The RAND() Function

The RANDBETWEEN() Function

From Here

Chapter 12 Working with Statistical Functions

Understanding Descriptive Statistics

Counting Items with the COUNT() Function

Calculating Averages

The AVERAGE() Function

The MEDIAN() Function

The MODE() Function

Calculating the Weighted Mean

Calculating Extreme Values

The MAX() and MIN() Functions

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 Range

Calculating the Variance

Calculating the Standard Deviation

Working with Frequency Distributions

The FREQUENCY() Function

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

Working with Histograms

Using the Random Number Generation Tool

Working with Rank and Percentile

From Here

Part III: Building Business Models

Chapter 13 Analyzing Data with Tables

Converting a Range to a Table

Basic Table Operations

Sorting a Table

Performing a More Complex Sort

Sorting a Table in Natural Order

Sorting on Part of a Field

Sorting Without Articles

Filtering Table Data

Using Filter Lists to Filter a Table

Using Complex Criteria to Filter a Table

Entering Computed Criteria

Copying Filtered Data to a Different Range

Referencing Tables in Formulas

Using Table Specifiers

Entering Table Formulas

Excel’s Table Functions

About Table Functions

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  

From Here

Chapter 14 Analyzing Data with PivotTables

What Are PivotTables?

How PivotTables Work

Some PivotTable Terms

Building 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

Hiding PivotTable Subtotals

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

Creating a Calculated Field

Creating a Calculated Item

Case Study: Using PivotTable Results in a Worksheet Formula  

From Here

Chapter 15 Using Excel’s Business-Modeling Tools

Using What-If Analysis

Setting Up a One-Input Data Table

Adding More Formulas to the Input Table

Setting Up a Two-Input Table

Editing a Data Table

Working with Goal Seek

How Does Goal Seek Work?

Running Goal Seek

    Optimizing Product Margin

A Note About Goal Seek’s Approximations

Performing a Break-Even Analysis

Solving Algebraic Equations

Working with Scenarios

Understanding Scenarios

Setting Up Your Worksheet for Scenarios

Adding a Scenario

Displaying a Scenario

Editing a Scenario

Merging Scenarios

Generating a Summary Report

Deleting a Scenario

From Here

Chapter 16 Using Regression to Track Trends and Make Forecasts

Choosing a Regression Method

Using Simple Regression on Linear Data

Analyzing Trends Using Best-Fit Lines

Making Forecasts

Case Study: Trend Analysis and Forecasting for a Seasonal Sales Model  

About the Forecast Workbook

Calculating a Normal Trend

Calculating the Forecast Trend

Calculating the Seasonal Trend

Computing the Monthly Seasonal Indexes

Calculating the Deseasoned Monthly Values

Calculating the Deseasoned Trend

Calculating the Reseasoned Trend

Calculating the Seasonal Forecast

    Working with Quarterly Data

Using Simple Regression on Nonlinear Data

Working with an Exponential Trend

Working with a Logarithmic Trend

Working with a Power Trend

Using Polynomial Regression Analysis

Using Multiple Regression Analysis

From Here

Chapter 17 Solving Complex Problems with Solver

Some Background on Solver

The Advantages of Solver

When Do You Use Solver?

Loading Solver

Using Solver

Adding Constraints

Saving a Solution as a Scenario

Setting Other Solver Options

Selecting the Method Solver Uses

Controlling How Solver Works

Working with Solver Models

Making Sense of Solver’s Messages

Case Study: Solving the Transportation Problem  

Displaying Solver’s Reports

The Answer Report

The Sensitivity Report

The Limits Report

From Here

Part IV: Building Financial Formulas

Chapter 18 Building Loan Formulas

Understanding the Time Value of Money

Calculating the Loan Payment

Loan Payment Analysis

Working with a Balloon Loan

Calculating Interest Costs, Part I

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 the Loan

Calculating the Interest Rate Required for a Loan

Calculating How Much You Can Borrow

Case Study: Working with Mortgages  

Building a Variable-Rate Mortgage Amortization Schedule

Allowing for Mortgage Principal Paydowns

From Here

Chapter 19 Building Investment Formulas

Working with Interest Rates

Understanding Compound Interest

Nominal Versus Effective Interest

Converting Between the Nominal Rate and the Effective Rate

Calculating the Future Value

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  

From Here

Chapter 20 Building Discount Formulas

Calculating the Present Value

Taking Inflation into Account

Calculating Present Value Using PV()

Income Investing Versus Purchasing a Rental Property

Buying Versus Leasing

Discounting Cash Flows

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

Discounted Payback Period

Calculating the Internal Rate of Return

Using the IRR() Function

Calculating the Internal Rate of Return for Nonperiodic Cash Flows

Calculating Multiple Internal Rates of Return

Case Study: Publishing a Book  

Per-Unit Constants

Operating Costs and Sales

Cash Flow

Cash-Flow Analysis

From Here

 

 

9780789748676   TOC   1/25/2013