Paul McFedries' Web Home



Formulas and Functions with Microsoft Excel 2003
Table of Contents

Chapter 1—Getting the Most Out of Ranges
A Review of Excel's Range Selection Techniques
Selecting a Range with the Mouse
Selecting a Contiguous Range with the Mouse
Selecting a Row or Column with the Mouse
Selecting a Range in Extend Mode with the Mouse
Selecting a Noncontiguous Range with the Mouse
Mouse Range Selection Tricks
Selecting Cell Ranges with the Keyboard
Selecting a Contiguous Range with the Keyboard
Selecting a Row or a Column with the Keyboard
Selecting a Noncontiguous Range with the Keyboard
Keyboard Range Selection Tricks
Working with 3-D Ranges
Advanced Range Selection Techniques
Selecting a Range Using Go To
Using the Go To Special Dialog Box
Selecting Cells by Type
Selecting Adjacent Cells
Selecting Cells by Differences
Selecting Cells by Reference
Other Go To Special Options
Shortcut Keys for Selecting Via Go To
Data Entry in a Range
Filling a Range
Using the Fill Handle
Using AutoFill to Create Text and Numeric Series
Creating a Custom AutoFill List
Filling a Range
Creating a Series
Copying a Range
Using Drag-and-Drop to Copy a Range
Copying a Range with the Copy Command
Making Multiple Copies of a Range
Inserting a Copy of a Range
Advanced Range Copying
Copying Selected Cell Attributes
Combining the Source and Destination Arithmetically
Transposing Rows and Columns
Moving a Range
Using Drag-and-Drop to Move a Range
Using the Menu Commands to Move a Range
Inserting and Deleting a Range
Inserting an Entire Row or Column
Inserting a Row or Column with the Fill Handle
Inserting a Cell or Range
Inserting a Range with the Fill Handle
Deleting an Entire Row or Column
Deleting a Cell or Range
Clearing a Range
Clearing a Range with the Fill Handle
Using Excel's Reference Operators
Using the Range Operator
Using the Intersection Operator
Using the Union Operator

Chapter 2—Using Range Names
Defining a Range Name
Working with the Name Box
Using the Define Name Dialog Box
Defining Sheet-Level Range Names
Assigning a Name to a 3-D Range
Entering a 3-D Range Name Manually
Entering a 3-D Range Name By Selecting the Range
Using Worksheet Text to Define Names
Naming Constants
Working with Range Names
Referring to a Range Name
Navigating Using Range Names
Pasting a List of Range Names in a Worksheet
Editing a Range Name's Coordinates
Adjusting Range Name Coordinates Automatically
Solution #1: Include a Blank Cell at the End of the Range
Solution #2: Name the Entire Row or Column
Changing a Range Name
Deleting a Range Name
Range Names and the Reference Operators
Using Names with the Range Operator
Using Names with the Intersection Operator

Chapter 3—Building Basic Formulas
Understanding Formula Basics
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
Converting a Formula to a Value
Working with Range Names in Formulas
Pasting a Name into a Formula
Applying Names to Formulas
Ignoring Relative and Absolute References When Applying Names
Using Row and Column Names When Applying Names
Naming Formulas
Working with Links in Formulas
Understanding External References
Updating Links
Editing Links
Formatting Numbers, Dates, and Times
Numeric Display Formats
Changing Numeric Formats
Customizing Numeric Formats
Hiding Zeros
Using Condition Values
Date and Time Display Formats
Customizing Date and Time Formats
Deleting Custom Formats

Chapter 4—Creating Advanced Formulas
Working with Arrays
Using Array Formulas
Understanding Array Formulas
Array Formulas That Operate on Multiple Ranges
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
Using the Forms Toolbar
Adding a Control to a Worksheet
Linking a Control to a Cell Value
Understanding the Worksheet Controls
Group Boxes
Option Buttons
Check Boxes
List Boxes and Combo Boxes
Scroll Bars and Spinners

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

Part 2—Harnessing the Power of Functions

Chapter 6—Using 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 Functions

Chapter 7—Working with Text Functions
Working with Characters and Codes
The CHAR() Function
Generating the ANSI Character Set
Generating a Series of Letters
The CODE() Function
Generating a Series of Letters Starting from Any Letter
Converting Text
The LOWER() Function
The UPPER() Function
The PROPER() 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
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
Generating Account Numbers, Part 2
Removing Line Feeds

Chapter 8—Working with Logical and Information Functions
Adding Intelligence with Logical Functions
Using the IF() Function
IF(): The Simplest Case
Handling a FALSE Result
Avoiding Division by Zero
Performing Multiple Logical Tests
Nesting IF() Functions
Calculating Tiered Bonuses
The AND() Function
Slotting Values Into Categories
The OR() Function
Combining Logical Functions with Arrays
Applying a Condition Across a Range
Operating Only on Cells That Meet a Condition
Determining Whether a Value Appears in a List
Counting Occurrences in a Range
Determining Where a Value Appears in a List
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 IS Functions
Counting the Number of Blanks in a Range
Checking a Range for Non-Numeric Values
Counting the Number of Errors in a Range
Ignoring Errors When Working with a Range
Avoiding Text Formula Errors

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
Performing Range Lookups
Looking Up a Customer Discount Rate
Looking Up a Tax Rate
Finding Exact Matches
Looking Up a Customer Account Number
Combining Exact-Match Lookups with In-Cell Dropdowns
Advanced Lookup Operations
The MATCH() and INDEX() Functions
Looking Up a Value Using Worksheet List Boxes
Using Any Column as the Lookup Column
Creating Row-and-Column Lookups
Creating Multiple-Column Lookups

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
TODAY(): Returning the Current Date
DATE(): Returning Any Date
DATEVALUE(): Converting a String to a Date
Returning Parts of a Date
The YEAR() Function
The MONTH() Function
The DAY() Function
The WEEKDAY() Function
The WEEKNUM() Function
Returning a Date X Years, Months, or Days From Now
A Workday Alternative: the WORKDAY() Function
Adding X Months: A Problem
The EDATE() Function
The EOMONTH() Function
Returning the Last Day of Any Month
Determining a Person's Birthday Given Their Birth Date
Returning the Date of the Nth Occurrence of a Weekday in a Month
Calculating Holiday Dates
Calculating the Julian Date
Calculating the Difference Between Two Dates
Calculating a Person's Age
The DATEDIF() Function
Calculating a Person's Age, Part 2
NETWORKDAYS(): Calculating the Number of Workdays Between Two Dates
DAYS360(): Calculating Date Differences Using a 360-Day Year
YEARFRAC(): Returning the Fraction of a Year Between Two Dates
Using Excel's Time Functions
Returning a Time
NOW(): Returning the Current Time
TIME(): Returning Any Time
TIMEVALUE(): Converting a String to a Time
Returning Parts of a Time
The HOUR() Function
The MINUTE() Function
The SECOND() Function
Returning a Time X Hours, Minutes, or Seconds From Now
Summing Time Values
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

Chapter 11—Working with Math Functions
Understanding Excel's Rounding Functions
The ROUND() Function
The MROUND() Function
The ROUNDDOWN() and ROUNDUP() Functions
The CEILING() and FLOOR() 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
Special Case #2: Summing Only Odd Rows
Special Case #2: Summing Only Even Rows
Determining Whether a Year Is a Leap Year
Creating Ledger Shading
Generating Random Numbers
The RAND() Function
Generating Random N-Digit Numbers
Generating a Random Letter
Sorting Values Randomly
The RANDBETWEEN() Function

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 with the VAR() Function
Calculating the Standard Deviation with the STDEVP() and STDEV() Functions
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

Part 3—Building Business Models

Chapter 13—Analyzing Data with Lists
Converting a Range to a List
Basic List Operations
Sorting a List
Sorting on More Than Three Keys
Sorting a List in Natural Order
Sorting on Part of a Field
Sorting Without Articles
Filtering List Data
Using AutoFilter to Filter a List
AutoFilter Criteria Options
Setting Up Custom AutoFilter Criteria
Showing Filtered Records
Using Complex Criteria to Filter a List
Setting Up a Criteria Range
Filtering a List with a Criteria Range
Entering Compound Criteria
Entering Computed Criteria
Copying Filtered Data to a Different Range
Summarizing List Data
Creating Automatic Subtotals
Setting Up a List for Automatic Subtotals
Displaying Subtotals
Adding More Subtotals
Nesting Subtotals
Working with a Subtotal's Outline Symbols
Removing Subtotals
Excel's List Functions
About List Functions
List Functions That Don't Require a Criteria Range
Using COUNTIF()
Using SUMIF()
List Functions That Require a Criteria Range
Using DAVERAGE()
Using DGET()
Case Study
Applying Statistical List Functions to a Defects Database

Chapter 14—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
Goal Seeking with Charts
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

Chapter 15—Using Regression to Track Trends and Make Forecasts
Choosing a Regression Method
Using Simple Regression on Linear Data
Analyzing Trends Using Best-Fit Lines
Plotting a Best-Fit Trendline
Understanding the Regression Equation
Understanding R2
Calculating Best-Fit Values Using TREND()
Calculating Best-Fit Values Using LINEST()
Analyzing the Sales Versus Advertising Trend
Making Forecasts
Plotting Forecasted Values
Extending a Linear Trend with the Fill Handle
Extending a Linear Trend Using the Series Command
Forecasting with the Regression Equation
Forecasting with TREND()
Forecasting with LINEST()
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
Plotting a Exponential Trendline
Calculating Exponential Trend and Forecast Values
Exponential Trending and Forecasting Using the GROWTH() Function
Working with a Logarithmic Trend
Plotting a Logarithmic Trendline
Calculating Logarithmic Trend and Forecast Values
Working with a Power Trend
Plotting a Power Trendline
Calculating Power Trend and Forecast Values
Using Polynomial Regression Analysis
Plotting a Polynomial Trendline
Calculating Polynomial Trend and Forecast Values
Using Multiple Regression Analysis

Chapter 16—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
Controlling Solver
Selecting the Method Used by Solver
Working with Solver Models
Making Sense of Solver's Messages
A Solver Example
The Transportation Problem
Displaying Solver's Reports
The Answer Report
The Sensitivity Report
The Limits Report

Part 4—Building Financial Formulas

Chapter 17—Building Loan Formulas
Understanding the Time Value of Money
Calculating the Loan Payment
Loan Payment Analysis
Working with a Balloon Loan
Calculating Interest Costs
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

Chapter 18—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

Chapter 19—Working with Bonds
Glossary of Bond Terms
Calculating Bond Yields
The YIELD() Function
The ODDFYIELD() and ODDLYIELD() Functions
Calculating Bond Prices
The PRICE() Function
The ODDFPRICE() and ODDLPRICE() Functions
Calculating Bond Duration
Calculating Bond Principal at Maturity
Working with Coupons
Calculating the Coupon Payment
Working with Coupon Dates
Calculating the Accrued Bond Interest
Calculating the Remaining Bond Interest
Working with Zero-Coupon Bonds
Working with Treasury Bills
Calculating the T-Bill Yield
Calculating the T-Bill Price

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 Non-Periodic 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 Non-Periodic 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

Copyright © 1995-2008 Paul McFedries and Logophilia Limited