Excel 2016 Formulas and Functions: Includes Content Update Program

Купить бумажную книгу и читать

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 range names and array formulas

• 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

In addition, this book is part of Que’s exciting Content Update Program. As Microsoft updates features of Excel 2016, sections of this book will be updated or new sections will be added to match the updates to the software. The updates will be delivered to you via a FREE Web Edition of this book, which can be accessed with any Internet connection.

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

About the Author

Paul McFedries is an Excel expert and full-time technical writer. Paul has been authoring computer books since 1991 and has more than 85 books to his credit, which combined have sold more than 4 million copies worldwide. His titles include the Que Publishing books My Office 2016, Windows 10 In Depth (with coauthor Brian Knittel), and PCs for Grownups, 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.

Sample Pages

Download the sample pages (includes Chapter 3 and Index)

Table of Contents

Introduction 1

PART I : MASTERING EXCEL RANGES AND FORMULAS

Chapter 1 Getting the Most Out of Ranges 5

Advanced Range-Selection Techniques 5

Mouse Range-Selection Tricks 6

Keyboard Range-Selection Tricks 7

Working with 3D Ranges 7

Selecting a Range Using Go To 8

Using the Go To Special Dialog Box 9

Data Entry in a Range 14

Filling a Range 14

Using the Fill Handle 15

Flash-Filling a Range 18

Creating a Series 20

Advanced Range Copying and Pasting 21

Pasting Selected Cell Attributes 22

Combining Two Ranges Arithmetically 23

Transposing Rows and Columns 24

Clearing a Range 25

Applying Conditional Formatting to a Range 25

Creating Highlight Cells Rules 26

Creating Top/Bottom Rules 27

Adding Data Bars 29

Adding Color Scales 32

Adding Icon Sets 33

Chapter 2 Using Range Names 37

Defining a Range Name 38

Working with the Name Box 39

Using the New Name Dialog Box 40

Changing the Scope to Define Sheet-Level Names 41

Using Worksheet Text to Define Names 41

Naming Constants 44

Working with Range Names 45

Referring to a Range Name 45

Working with AutoComplete for Range Names 47

Navigating Using Range Names 47

Pasting a List of Range Names in a Worksheet 48

Displaying the Name Manager 48

Filtering Names 48

Editing a Range Name’s Coordinates 49

Adjusting Range Name Coordinates Automatically 49

Changing a Range Name 51

Deleting a Range Name 51

Using Names with the Intersection Operator 51

Chapter 3 Building Basic Formulas 53

Understanding Formula Basics 53

Formula Limits in Excel 2016 54

Entering and Editing Formulas 54

Using Arithmetic Formulas 55

Using Comparison Formulas 56

Using Text Formulas 57

Using Reference Formulas 57

Understanding Operator Precedence 57

The Order of Precedence 58

Controlling the Order of Precedence 58

Controlling Worksheet Calculation 60

Copying and Moving Formulas 62

Understanding Relative Reference Format 62

Understanding Absolute Reference Format 64

Copying a Formula Without Adjusting Relative References 65

Displaying Worksheet Formulas 65

Displaying All Worksheet Formulas 65

Displaying a Cell’s Formula by Using FORMULATEXT() 65

Converting a Formula to a Value 66

Working with Range Names in Formulas 67

Pasting a Name into a Formula 67

Applying Names to Formulas 68

Naming Formulas 70

Working with Links in Formulas 71

Understanding External References 72

Updating Links 73

Changing the Link Source 73

Formatting Numbers, Dates, and Times 74

Numeric Display Formats 74

Date and Time Display Formats 83

Deleting Custom Formats 85

Chapter 4 Creating Advanced Formulas 87

Working with Arrays 87

Using Array Formulas 88

Using Array Constants 91

Functions That Use or Return Arrays 91

Using Iteration and Circular References 93

Consolidating Multisheet Data 95

Consolidating by Position 95

Consolidating by Category 98

Applying Data-Validation Rules to Cells 100

Using Dialog Box Controls on a Worksheet 103

Displaying the Developer Tab 103

Using the Form Controls 103

Adding a Control to a Worksheet 104

Linking a Control to a Cell Value 104

Understanding the Worksheet Controls 105

Chapter 5 Troubleshooting Formulas 111

Understanding Excel’s Error Values 112

#DIV/0! 112

#N/A 113

#NAME? 113

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

#NULL! 115

#NUM! 115

#REF! 115

#VALUE! 115

Fixing Other Formula Errors 116

Missing or Mismatched Parentheses 116

Erroneous Formula Results 117

Fixing Circular References 118

Handling Formula Errors with IFERROR() 118

Using the Formula Error Checker 119

Choosing an Error Action 120

Setting Error Checker Options 121

Auditing a Worksheet 123

Understanding Auditing 124

Tracing Cell Precedents 125

Tracing Cell Dependents 125

Tracing Cell Errors 125

Removing Tracer Arrows 125

Evaluating Formulas 126

Watching Cell Values 126

PART II : HARNESSING THE POWER OF FUNCTIONS

Chapter 6 Understanding Functions 129

About Excel’s Functions 130

The Structure of a Function 130

Typing a Function into a Formula 132

Using the Insert Function Feature 134

Loading the Analysis ToolPak 136

Chapter 7 Working with Text Functions 139

Excel’s Text Functions 139

Working with Characters and Codes 141

The CHAR() Function 141

The CODE() Function 144

Converting Text 144

The LOWER() Function 145

The UPPER() Function 145

The PROPER() Function 145

The NUMBERVALUE() Function 145

Formatting Text 146

The DOLLAR() Function 146

The FIXED() Function 147

The TEXT() Function 147

Displaying When a Workbook Was Last Updated 148

Manipulating Text 149

Removing Unwanted Characters from a String 149

The REPT() Function: Repeating a Character or String 150

Extracting a Substring 152

Converting Text to Sentence Case 153

A Date-Conversion Formula 154

Case Study: Generating Account Numbers, Part I 154

Searching for Substring 155

The FIND() and SEARCH() Functions 155

Extracting a First Name or Last Name 156

Extracting First Name, Last Name, and Middle Initial 157

Determining the Column Letter 157

Substituting One Substring for Another 158

The REPLACE() Function 159

The SUBSTITUTE() Function 159

Removing a Character from a String 160

Removing Two Different Characters from a String 160

Case Study: Generating Account Numbers, Part II 161

Removing Line Feeds 161

Chapter 8 Working with Logical and Information Functions 163

Adding Intelligence with Logical Functions 163

Using the IF() Function 164

Performing Multiple Logical Tests 167

Combining Logical Functions with Arrays 173

Case Study: Building an Accounts Receivable Aging Worksheet 178

Getting Data with Information Functions 181

The CELL() Function 182

The ERROR.TYPE() Function 184

The INFO() Function 186

The SHEET() and SHEETS() Functions 186

The IS Functions 187

Chapter 9 Working with Lookup Functions 191

Excel’s Lookup Functions 191

Understanding Lookup Tables 192

The CHOOSE() Function 193

Determining the Name of the Day of the Week 194

Determining the Month of the Fiscal Year 194

Calculating Weighted Questionnaire Results 195

Integrating CHOOSE() and Worksheet Option Buttons 195

Looking Up Values in Tables 196

The VLOOKUP() Function 197

The HLOOKUP() Function 197

Returning a Customer Discount Rate with a Range Lookup 198

Returning a Tax Rate with a Range Lookup 199

Finding Exact Matches 200

Advanced Lookup Operations 201

Chapter 10 Working with Date and Time Functions 207

How Excel Deals with Dates and Times 207

Entering Dates and Times 208

Excel and Two-Digit Years 209

Using Excel’s Date Functions 210

Returning a Date 212

Returning Parts of a Date 213

Calculating the Difference Between Two Dates 223

Using Excel’s Time Functions 227

Returning a Time 228

Returning Parts of a Time 229

Calculating the Difference Between Two Times 231

Case Study: Building an Employee Time Sheet 231

Chapter 11 Working with Math Functions 237

Excel’s Math and Trig Functions 237

Understanding Excel’s Rounding Functions 241

The ROUND() Function 241

The MROUND() Function 242

The ROUNDDOWN() and ROUNDUP() Functions 242

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

The EVEN() and ODD() Functions 245

The INT() and TRUNC() Functions 245

Case Study: Rounding Billable 247

Summing Values 247

The SUM() Function 247

Calculating Cumulative Totals 248

Summing Only the Positive or Negative Values in a Range 249

The MOD() Function 249

A Better Formula for Time Differences 250

Summing Every nth Row 250

Determining Whether a Year Is a Leap Year 251

Creating Ledger Shading 251

Generating Random Numbers 253

The RAND() Function 253

The RANDBETWEEN() Function 255

Chapter 12 Working with Statistical Functions 257

Excel’s Statistical Functions 257

Understanding Descriptive Statistics 260

Counting Items with the COUNT() Function 261

Calculating Averages 262

The AVERAGE() Function 262

The MEDIAN() Function 262

The MODE() Function 263

Calculating the Weighted Mean 263

Calculating Extreme Values 264

The MAX() and MIN() Functions 264

The LARGE() and SMALL() Functions 266

Performing Calculations on the Top k Values 266

Performing Calculations on the Bottom k Values 267

Calculating Measures of Variation 267

Calculating the Range 268

Calculating the Variance 268

Calculating the Standard Deviation 269

Working with Frequency Distributions 270

The FREQUENCY() Function 271

Understanding the Normal Distribution and the NORMDIST() Function 272

The Shape of the Curve I: The SKEW() Function 274

The Shape of the Curve II: The KURT() Function 275

Using the Analysis ToolPak Statistical Tools 276

Using the Descriptive Statistics Tool 279

Determining the Correlation Between Data 280

Working with Histograms 283

Using the Random Number Generation Tool 285

Working with Rank and Percentile 288

PART III : BUILDING BUSINESS MODELS

Chapter 13 Analyzing Data with Tables 291

Planning an Excel Table 291

Converting a Range to a Table 292

Basic Table Operations 294

Sorting a Table 295

Performing a More Complex Sort 296

Sorting a Table in Natural Order 297

Sorting on Part of a Field 298

Sorting Without Articles 299

Filtering Table Data 300

Using Filter Lists to Filter a Table 300

Using Complex Criteria to Filter a Table 304

Entering Computed Criteria 307

Copying Filtered Data to a Different Range 308

Referencing Tables in Formulas 309

Using Table Specifiers 309

Entering Table Formulas 311

Excel’s Table Functions 313

About Table Functions 313

Table Functions That Don’t Require a Criteria Range 313

Table Functions That Accept Multiple Criteria 317

Table Functions That Require a Criteria Range 319

Case Study: Applying Statistical Table Functions to a Defects Database 322

Chapter 14 Analyzing Data with PivotTables 325

What Are PivotTables? 325

How PivotTables Work 326

Some PivotTable Terms 328

Building PivotTables 329

Building a PivotTable from a Table or Range 329

Building a PivotTable from an External Database 332

Working with and Customizing a PivotTable 333

Working with PivotTable Subtotals 333

Hiding PivotTable Grand Totals 334

Hiding PivotTable Subtotals 334

Customizing the Subtotal Calculation 334

Changing the Data Field Summary Calculation 335

Using a Difference Summary Calculation 336

Using a Percentage Summary Calculation 337

Using a Running Total Summary Calculation 340

Using an Index Summary Calculation 341

Creating Custom PivotTable Calculations 342

Creating a Calculated Field 344

Creating a Calculated Item 346

Using PivotTable Results in a Worksheet Formula 347

Chapter 15 Using Excel’s Business Modeling Tools 349

Using What-If Analysis 349

Setting Up a One-Input Data Table 350

Adding More Formulas to the Input Table 352

Setting Up a Two-Input Data Table 353

Editing a Data Table 355

Working with Goal Seek 355

How Does Goal Seek Work? 355

Running Goal Seek 356

Optimizing Product Margin 358

A Note About Goal Seek’s Approximations 358

Performing a Break-Even Analysis 360

Solving Algebraic Equations 360

Working with Scenarios 362

Understanding Scenarios 362

Setting Up Your Worksheet for Scenarios 363

Adding a Scenario 364

Displaying a Scenario 365

Editing a Scenario 366

Merging Scenarios 367

Generating a Summary Report 367

Deleting a Scenario 369

Chapter 16 Using Regression to Track Trends and Make Forecasts 371

Choosing a Regression Method 372

Using Simple Regression on Linear Data 372

Analyzing Trends Using Best-Fit Lines 373

Making Forecasts 380

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

Using Simple Regression on Nonlinear Data 393

Working with an Exponential Trend 394

Working with a Logarithmic Trend 399

Working with a Power Trend 401

Using Polynomial Regression Analysis 403

Using Multiple Regression Analysis 407

Chapter 17 Solving Complex Problems with Solver 411

Some Background on Solver 411

The Advantages of Solver 412

When Do You Use Solver? 412

Loading Solver 413

Using Solver 413

Adding Constraints 416

Saving a Solution as a Scenario 418

Setting Other Solver Options 418

Selecting the Method Solver Uses 419

Controlling How Solver Works 419

Working with Solver Models 422

Making Sense of Solver’s Messages 424

Case Study: Solving the Transportation Problem 425

Displaying Solver’s Reports 427

The Answer Report 427

The Sensitivity Report 429

The Limits Report 430

PART IV : BUILDING FINANCIAL FORMULAS

Chapter 18 Building Loan Formulas 433

Understanding the Time Value of Money 433

Calculating a Loan Payment 435

Loan Payment Analysis 435

Working with a Balloon Loan 436

Calculating Interest Costs, Part 1 436

Calculating the Principal and Interest 437

Calculating Interest Costs, Part 2 438

Calculating Cumulative Principal and Interest 439

Building a Loan Amortization Schedule 440

Building a Fixed-Rate Amortization Schedule 440

Building a Dynamic Amortization Schedule 441

Calculating the Term of a Loan 443

Calculating the Interest Rate Required for a Loan 445

Calculating How Much You Can Borrow 446

Case Study: Working with Mortgages 447

Chapter 19 Building Investment Formulas 453

Working with Interest Rates 453

Understanding Compound Interest 454

Nominal Versus Effective Interest 454

Converting Between the Nominal Rate and the Effective Rate 455

Calculating the Future Value 456

The Future Value of a Lump Sum 456

The Future Value of a Series of Deposits 457

The Future Value of a Lump Sum Plus Deposits 458

Working Toward an Investment Goal 458

Calculating the Required Interest Rate 458

Calculating the Required Number of Periods 459

Calculating the Required Regular Deposit 460

Calculating the Required Initial Deposit 461

Calculating the Future Value with Varying Interest Rates 461

Case Study: Building an Investment Schedule 462

Chapter 20 Building Discount Formulas 467

Calculating the Present Value 468

Taking Inflation into Account 468

Calculating Present Value Using PV() 469

Income Investing Versus Purchasing a Rental Property 470

Buying Versus Leasing 471

Discounting Cash Flows 472

Calculating the Net Present Value 473

Calculating Net Present Value Using NPV() 474

Net Present Value with Varying Cash Flows 475

Net Present Value with Nonperiodic Cash Flows 476

Calculating the Payback Period 477

Simple Undiscounted Payback Period 477

Exact Undiscounted Payback Point 478

Discounted Payback Period 479

Calculating the Internal Rate of Return 479

Using the IRR() Function 480

Calculating the Internal Rate of Return for Nonperiodic Cash Flows 480

Calculating Multiple Internal Rates of Return 481

Case Study: Publishing a Book 482

Index 497