Excel 2013 Formulas and Functions

Description

• Copyright 2013
• Dimensions: 7" x 9-1/8"
• Edition: 1st

• eBook (Watermarked)
• ISBN-10: 0-13-326072-0
• ISBN-13: 978-0-13-326072-4

Master core Excel 2013 tools for building powerful, reliable spreadsheets!

Excel expert Paul McFedries shows how to use Excel 2013’s core features to solve problems and get the answers you need. Using real-world examples, McFedries helps you get the absolute most out of features and improvements ranging from FlashFill to Excel’s newest functions. Along the way, you discover the fastest, best ways to handle essential day-to-day tasks ranging from generating account numbers to projecting the impact of inflation.

Becoming an Excel expert has never been easier! You’ll find crystal-clear instructions; insider insights; even complete step-by-step projects for building timesheets, projecting cash flow, aging receivables, analyzing defects, and more..

•   Quickly create powerful spreadsheets with FlashFill

•   Use conditional formatting to instantly reveal anomalies, problems, or opportunities

•   Analyze your data with standard tables and PivotTables

•   Use complex criteria to filter data in lists

•   Understand correlations between data

•   Perform sophisticated what-if analyses

•   Use regression to track trends and make forecasts

•   Build loan, investment, and discount formulas

•   Validate data, troubleshoot problems, and build more accurate, trustworthy spreadsheets

About MrExcel Library:  Every book in the MrExcel Library pinpoints a specific set of crucial Excel tasks and presents focused skills and examples for performing them rapidly and effectively. Selected by Bill Jelen, Microsoft Excel MVP and mastermind behind the leading Excel solutions website MrExcel.com, these books will

•   Dramatically increase your productivity—saving you 50 hours a year or more

•   Present proven, creative strategies for solving real-world problems

•   Show you how to get great results, no matter how much data you have

•   Help you avoid critical mistakes that even experienced users make

CATEGORY: Spreadsheets

COVERS: Microsoft Office Excel 2013



Sample Content

Table of Contents

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



Updates

Submit Errata

