Excel® 2013 Formulas
Table of Contents
Part II: Using Functions in Your Formulas
Part V: Miscellaneous Formula Techniques
Part VI: Developing Custom Worksheet Functions
About the Power Utility Pak Offer
Chapter 1: Excel in a Nutshell
Macro sheets and dialog sheets
Shortcut menus and the mini toolbar
Worksheet Formulas and Functions
Protecting formulas from being overwritten
Protecting a workbook's structure
Password-protecting a workbook
Chapter 2: Basic Facts about Formulas
Creating an absolute or a mixed reference
Referencing other sheets or workbooks
Making an Exact Copy of a Formula
Dealing with Circular References
Referencing names from another workbook
Shortcuts for Creating Cell and Range Names
Creating names using the Name box
Creating names from text in cells
Naming entire rows and columns
Working with Range and Cell Names
Using the intersection operators with names
Using the range operator with names
Referencing a single cell in a multicell named range
Applying names to existing formulas
Applying names automatically when creating a formula
How Excel Maintains Cell and Range Names
Name problems when copying sheets
Name problems when deleting sheets
The Secret to Understanding Names
Using worksheet functions in named formulas
Using cell and range references in named formulas
Using named formulas with relative references
Advanced Techniques That Use Names
Using the INDIRECT function with a named range
Using arrays in named formulas
Creating a dynamic named formula
Using an XLM macro in a named formula
Part II: Using Functions in Your Formulas
Chapter 4: Introducing Worksheet Functions
Perform otherwise impossible calculations
Provide decision-making capability
Full-column or full-row as arguments
Ways to Enter a Function into a Formula
Using the Function Library commands
Using the Insert Function dialog box
More tips for entering functions
Lookup and reference functions
How many characters in a cell?
Determining whether a cell contains text
Determining whether two strings are identical
Displaying formatted values as text
Displaying formatted currency values as text
Removing excess spaces and nonprinting characters
Counting characters in a string
Repeating a character or string
Extracting characters from a string
Replacing text with other text
Finding and searching within a string
Searching and replacing within a string
Counting specific characters in a cell
Counting the occurrences of a substring in a cell
Expressing a number as an ordinal
Determining a column letter for a column number
Extracting a filename from a path specification
Extracting the first word of a string
Extracting the last word of a string
Extracting all but the first word of a string
Extracting first names, middle names, and last names
Counting the number of words in a cell
Chapter 6: Working with Dates and Times
How Excel Handles Dates and Times
Understanding date serial numbers
Understanding time serial numbers
Displaying any date with a function
Converting a non-date string to a date
Calculating the number of days between two dates
Calculating the number of work days between two dates
Offsetting a date using only work days
Calculating the number of years between two dates
Determining the day of the year
Determining the day of the week
Determining the week of the year
Determining the date of the most recent Sunday
Determining the first day of the week after a date
Determining the nth occurrence of a day of the week in a month
Counting the occurrences of a day of the week
Expressing a date as an ordinal number
Determining the last day of a month
Determining whether a year is a leap year
Converting a year to roman numerals
Displaying any time using a function
Calculating the difference between two times
Summing times that exceed 24 hours
Converting decimal hours, minutes, or seconds to a time
Adding hours, minutes, or seconds to a time
Working with non–time-of-day values
Chapter 7: Counting and Summing Techniques
Counting and Summing Worksheet Cells
Counting the total number of cells
Counting error values in a range
Counting cells with the COUNTIF function
Counting cells that meet multiple criteria
Counting the most frequently occurring entry
Counting the occurrences of specific text
Counting the number of unique values
Creating a frequency distribution
Summing a range that contains errors
Conditional Sums Using a Single Criterion
Summing values based on a different range
Summing values based on a text comparison
Summing values based on a date comparison
Conditional Sums Using Multiple Criteria
Chapter 8: Using Lookup Functions
Combining the MATCH and INDEX functions
Looking up a value to the left
Performing a case-sensitive lookup
Choosing among multiple lookup tables
Determining letter grades for test scores
Calculating a grade point average
Performing a two-column lookup
Determining the address of a value within a range
Looking up a value by using the closest match
Looking up a value using linear interpolation
Chapter 9: Working with Tables and Lists
Navigating and selecting in a table
Removing duplicate rows from a table
Specifying Advanced Filter Criteria
Chapter 10: Miscellaneous Calculations
Rounding to the nearest multiple
Working with fractional dollars
Using the INT and TRUNC functions
Rounding to an even or odd integer
Rounding to n significant digits
Area, Surface, Circumference, and Volume Calculations
Calculating the area and perimeter of a square
Calculating the area and perimeter of a rectangle
Calculating the area and perimeter of a circle
Calculating the area of a trapezoid
Calculating the area of a triangle
Calculating the surface and volume of a sphere
Calculating the surface and volume of a cube
Calculating the surface and volume of a rectangular solid
Calculating the surface and volume of a cone
Calculating the volume of a cylinder
Calculating the volume of a pyramid
Solving Simultaneous Equations
Working with Normal Distributions
Chapter 11: Borrowing and Investing Formulas
Worksheet functions for calculating loan information
Creating a loan amortization schedule
Calculating a loan with irregular payments
Future value of a single deposit
Present value of a series of payments
Future value of a series of deposits
Chapter 12: Discounting and Depreciation Formulas
Chapter 13: Financial Schedules
Creating Amortization Schedules
A simple amortization schedule
A dynamic amortization schedule
Summarizing Loan Options Using a Data Table
Financial Statements and Ratios
Chapter 14: Introducing Arrays
Understanding the Dimensions of an Array
One-dimensional horizontal arrays
One-dimensional vertical arrays
Selecting an array formula range
Expanding or contracting a multicell array formula
Using Multicell Array Formulas
Creating an array from values in a range
Creating an array constant from values in a range
Performing operations on an array
Generating an array of consecutive integers
Using Single-Cell Array Formulas
Counting characters in a range
Summing the three smallest values in a range
Counting text cells in a range
Eliminating intermediate formulas
Using an array in lieu of a range reference
Chapter 15: Performing Magic with Array Formulas
Working with Single-Cell Array Formulas
Summing a range that contains errors
Counting the number of error values in a range
Summing the n largest values in a range
Computing an average that excludes zeros
Determining whether a particular value appears in a range
Counting the number of differences in two ranges
Returning the location of the maximum value in a range
Finding the row of a value's nth occurrence in a range
Returning the longest text in a range
Determining whether a range contains valid values
Summing the digits of an integer
Summing every nth value in a range
Removing nonnumeric characters from a string
Determining the closest value in a range
Returning the last value in a column
Returning the last value in a row
Working with Multicell Array Formulas
Returning only positive values from a range
Returning nonblank cells from a range
Reversing the order of cells in a range
Sorting a range of values dynamically
Returning a list of unique items in a range
Displaying a calendar in a range
Part V: Miscellaneous Formula Techniques
Chapter 16: Importing and Cleaning Data
Importing a text file into a specified range
Change vertical data to horizontal data
Filling gaps in an imported report
Replacing or removing text in cells
Exporting to other file formats
Chapter 17: Charting Techniques
Understanding the SERIES Formula
Using names in a SERIES formula
Unlinking a chart series from its data range
Adding a linked picture to a chart
Displaying conditional colors in a column chart
Creating a comparative histogram
Identifying maximum and minimum values in a chart
Plotting mathematical functions
Working with nonlinear trendlines
Summary of trendline equations
Selecting a series from a drop-down list
Plotting the last n data points
Choosing a start date and number of points
Data Appropriate for a Pivot Table
Creating a Pivot Table Automatically
Creating a Pivot Table Manually
Specifying the location for the pivot table
Creating a Frequency Distribution
Creating a Calculated Field or Calculated Item
Filtering Pivot Tables with Slicers
Filtering Pivot Tables with a Timeline
Referencing Cells within a Pivot Table
Chapter 19: Conditional Formatting
Specifying Conditional Formatting
Formatting types you can apply
Conditional Formats That Use Graphics
Understanding relative and absolute references
Conditional formatting formula examples
Working with Conditional Formats
Copying cells that contain conditional formatting
Deleting conditional formatting
Locating cells that contain conditional formatting
Chapter 20: Using Data Validation
Specifying Validation Criteria
Types of Validation Criteria You Can Apply
Using Formulas for Data Validation Rules
Data Validation Formula Examples
Accepting a larger value than the previous cell
Accepting nonduplicate entries only
Accepting text that begins with a specific character
Accepting dates by the day of the week
Accepting only values that don't exceed a total
Chapter 21: Creating Megaformulas
Creating a Megaformula: A Simple Example
Using a megaformula to remove middle names
Using a megaformula to return a string's last space character position
Using a megaformula to determine the validity of a credit card number
The Pros and Cons of Megaformulas
Chapter 22: Tools and Methods for Debugging Formulas
Formula Problems and Solutions
Cells are filled with hash marks
Absolute/relative reference problems
Actual versus displayed values
Identifying cells of a particular type
Fixing circular reference errors
Using background error checking
Using Excel's Formula Evaluator
Part VI: Developing Custom Worksheet Functions
Saving Workbooks That Contain Macros
Introducing the Visual Basic Editor
Chapter 24: Function Procedure Basics
An Introductory VBA Function Example
Choosing a name for your function
Using the Insert Function Dialog Box
Specifying a function category
Testing and Debugging Your Functions
Using the VBA MsgBox statement
Using Debug.Print statements in your code
Calling the function from a Sub procedure
Setting a breakpoint in the function
Creating Add-Ins for Functions
Chapter 25: VBA Programming Concepts
An Introductory Example Function Procedure
Using Variables, Data Types, and Constants
Declaring multidimensional arrays
Looping blocks of instructions
Some useful properties of ranges
Chapter 26: VBA Custom Function Examples
Does a cell contain a formula?
Returning the application's name
Returning Excel's version number
Returning cell formatting information
Determining a Cell's Data Type
Generating random numbers that don't change
A function for a simple commission structure
A function for a more complex commission structure
Does the text match a pattern?
Does a cell contain a particular word?
Extracting the nth element from a string
Counting pattern-matched cells
Calculating the next day of the week
Working with dates before 1900
Returning the Last Nonempty Cell in a Column or Row
Returning the maximum value across all worksheets
Returning an array from a function
Returning an array of nonduplicated random integers
Using an indefinite number of arguments
Appendix 1: Excel Function Reference
Appendix 2: Using Custom Number Formats
Formatting numbers by using the Ribbon
Using shortcut keys to format numbers
Using the format cells dialog box to format numbers
Parts of a number format string
Displaying a negative sign on the right
Suppressing certain types of entries