Excel 2016 Formulas and Functions (includes Content Update Program) by McFedries, Paul -- Read -- Imperial Library of Trantor

Index

About This eBook Title Page Copyright Page Contents at a Glance Contents About the Author Dedication Acknowledgments We Want to Hear from You! Reader Services Introduction

What’s in the Book This Book’s Special Features

Part I: Mastering Excel Ranges and Formulas

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

Pasting 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

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 AutoComplete for Range Names 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

3. Building Basic Formulas

Understanding Formula Basics

Formula Limits in Excel 2016 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 by 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

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

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

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

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 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 REPT() Function: Repeating a Character or String Extracting a Substring Converting Text to Sentence Case A Date-Conversion Formula Case Study: Generating Account Numbers, Part I

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

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

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

9. Working with Lookup Functions

Excel’s 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

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

From Here

11. Working with Math Functions

Excel’s Math and Trig Functions Understanding Excel’s Rounding Functions

The ROUND() Function The MROUND() Function 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 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

12. Working with Statistical Functions

Excel’s 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

13. Analyzing Data with Tables

Planning an Excel Table 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

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

Using PivotTable Results in a Worksheet Formula From Here

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

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

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

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

18. Building Loan Formulas

Understanding the Time Value of Money Calculating a Loan Payment

Loan Payment Analysis Working with a Balloon Loan 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

From Here

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

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

From Here

Index Inside Front Cover Inside Back Cover Code Snippets

← Prev
Back
Next →

← Prev
Back
Next →