Excel 2016 Formulas

Michael Alexander is a Microsoft Certified Application Developer and author of several books on Microsoft Access and Excel. He runs a free tutorial site at datapigtechnologies.com. Dick Kusleika develops Access- and Excel-based solutions, and conducts Office training seminars in the U.S. and Australia. He writes the popular blog dailydoseofexcel.com. John Walkenbach is a renowned authority on Excel. He has written hundreds of articles, thirty-plus books, created the award-winning Power Utility Pak, and developed the popular spreadsheetpage.com.

Introduction xxvii

Part I: Understanding Formula Basics

Chapter 1: The Excel User Interface in a Nutshell 3

The Workings of Workbooks 3

Worksheets 4

Chart sheets 5

Macro sheets and dialog sheets 5

The Excel User Interface 5

The Ribbon 6

Backstage View 7

Shortcut menus and the mini toolbar 7

Dialog boxes 7

Customizing the UI 8

Task panes 9

Customizing onscreen display 9

Numeric formatting 9

Stylistic formatting 9

Protection Options 10

Securing access to the entire workbook 10

Limiting access to specific worksheet ranges 13

Protecting the workbook structure 16

Chapter 2: Basic Facts About Formulas 19

Entering and Editing Formulas 19

Formula elements 20

Entering a formula 20

Pasting names 22

Spaces and line breaks 22

Formula limits 23

Sample formulas 23

Editing formulas 24

Using Operators in Formulas 25

Reference operators 25

Sample formulas that use operators 26

Operator precedence 27

Nested parentheses 29

Calculating Formulas 30

Cell and Range References 30

Creating an absolute or a mixed reference 31

Referencing other sheets or workbooks 33

Copying or Moving Formulas 35

Making an Exact Copy of a Formula 36

Converting Formulas to Values 37

Hiding Formulas 39

Errors in Formulas 40

Dealing with Circular References 41

Goal Seeking 42

A goal seeking example 42

More about goal seeking 43

Chapter 3: Working with Names 45

What’s in a Name? 45

A Name’s Scope 46

Referencing names 47

Referencing names from another workbook 48

Conflicting names 48

The Name Manager 48

Creating names 49

Editing names 50

Deleting names 50

Shortcuts for Creating Cell and Range Names 50

The New Name dialog box 51

Creating names using the Name box 52

Creating names from text in cells 52

Naming entire rows and columns 54

Names created by Excel 55

Creating Multisheet Names 55

Working with Range and Cell Names 57

Creating a list of names 58

Using names in formulas 59

Using the intersection operators with names 59

Using the range operator with names 61

Referencing a single cell in a multicell named range 61

Applying names to existing formulas 62

Applying names automatically when creating a formula 63

Unapplying names 63

Names with errors 64

Viewing named ranges 64

Using names in charts 64

How Excel Maintains Cell and Range Names 65

Inserting a row or column 65

Deleting a row or a column 65

Cutting and pasting 65

Potential Problems with Names 66

Name problems when copying sheets 66

Name problems when deleting sheets 66

The Secret to Understanding Names 68

Naming constants 68

Naming text constants 69

Using worksheet functions in named formulas 70

Using cell and range references in named formulas 71

Using named formulas with relative references 72

Advanced Techniques That Use Names 75

Using the INDIRECT function with a named range 75

Using arrays in named formulas 77

Creating a dynamic named formula 78

Using an XLM macro in a named formula 80

Part II: Leveraging Excel Functions

Chapter 4: Introducing Worksheet Functions 85

What Is a Function? 85

Simplify your formulas 86

Perform otherwise impossible calculations 86

Speed up editing tasks 86

Provide decision-making capability 87

More about functions 87

Function Argument Types 88

Names as arguments 89

Full-column or full-row as arguments 89

Literal values as arguments 90

Expressions as arguments 90

Other functions as arguments 91

Arrays as arguments 91

Ways to Enter a Function into a Formula 91

Entering a function manually 91

Using the Function Library commands 93

Using the Insert Function dialog box 94

More tips for entering functions 96

Chapter 5: Manipulating Text 99

A Few Words About Text 99

How many characters in a cell? 99

Numbers as text 99

Text Functions 101

Determining whether a cell contains text 101

Working with character codes 102

Determining whether two strings are identical 105

Joining two or more cells 105

Displaying formatted values as text 106

Displaying formatted currency values as text 108

Removing excess spaces and nonprinting characters 108

Counting characters in a string 109

Repeating a character or string 109

Creating a text histogram 110

Padding a number 111

Changing the case of text 112

Extracting characters from a string 113

Replacing text with other text 113

Finding and searching within a string 114

Searching and replacing within a string 115

Advanced Text Formulas 115

Counting specific characters in a cell 116

Counting the occurrences of a substring in a cell 116

Removing trailing minus signs 116

Expressing a number as an ordinal 117

Determining a column letter for a column number 118

Extracting a filename from a path specification 118

Extracting the first word of a string 119

Extracting the last word of a string 119

Extracting all but the first word of a string 120

Extracting first names, middle names, and last names 120

Removing titles from names 122

Counting the number of words in a cell 122

Chapter 6: Working with Dates and Times 125

How Excel Handles Dates and Times 125

Understanding date serial numbers 126

Entering dates 127

Understanding time serial numbers 129

Entering times 130

Formatting dates and times 131

Problems with dates 133

Date-Related Functions 134

Displaying the current date 135

Displaying any date with a function 136

Generating a series of dates 137

Converting a nondate string to a date 138

Calculating the number of days between two dates 139

Calculating the number of work days between two dates 139

Offsetting a date using only work days 141

Calculating the number of years between two dates 141

Calculating a person’s age 142

Determining the day of the year 143

Determining the day of the week 144

Determining the week of the year 144

Determining the date of the most recent Sunday 144

Determining the first day of the week after a date 145

Determining the nth occurrence of a day of the week in a month 145

Counting the occurrences of a day of the week 146

Expressing a date as an ordinal number 147

Calculating dates of holidays 147

Determining the last day of a month 150

Determining whether a year is a leap year 151

Determining a date’s quarter 151

Converting a year to roman numerals 151

Time-Related Functions 152

Displaying the current time 152

Displaying any time using a function 153

Calculating the difference between two times 154

Summing times that exceed 24 hours 155

Converting from military time 157

Converting decimal hours, minutes, or seconds to a time 158

Adding hours, minutes, or seconds to a time 158

Converting between time zones 159

Rounding time values 160

Calculating Durations 161

Chapter 7: Counting and Summing Techniques 163

Counting and Summing Worksheet Cells 163

Other Counting Methods 165

Basic Counting Formulas 165

Counting the total number of cells 166

Counting blank cells 166

Counting nonblank cells 167

Counting numeric cells 167

Counting text cells 168

Counting nontext cells 168

Counting logical values 168

Counting error values in a range 168

Advanced Counting Formulas 169

Counting cells with the COUNTIF function 169

Counting cells that meet multiple criteria 170

Counting the most frequently occurring entry 173

Counting the occurrences of specific text 174

Counting the number of unique values 176

Creating a frequency distribution 178

Summing Formulas 184

Summing all cells in a range 184

Summing a range that contains errors 185

Computing a cumulative sum 186

Summing the “top n” values 187

Conditional Sums Using a Single Criterion 188

Summing only negative values 189

Summing values based on a different range 190

Summing values based on a text comparison 190

Summing values based on a date comparison 190

Conditional Sums Using Multiple Criteria 191

Using And criteria 191

Using Or criteria 192

Using And and Or criteria 193

Chapter 8: Using Lookup Functions 195

What Is a Lookup Formula? 195

Functions Relevant to Lookups 196

Basic Lookup Formulas 198

The VLOOKUP function 198

The HLOOKUP function 200

The LOOKUP function 201

Combining the MATCH and INDEX functions 202

Specialized Lookup Formulas 203

Looking up an exact value 204

Looking up a value to the left 206

Performing a case-sensitive lookup 207

Choosing among multiple lookup tables 207

Determining letter grades for test scores 208

Calculating a grade point average 209

Performing a two-way lookup 211

Performing a two-column lookup 212

Determining the address of a value within a range 213

Looking up a value by using the closest match 214

Looking up a value using linear interpolation 215

Chapter 9: Working with Tables and Lists 219

Tables and Terminology 219

A list example 220

A table example 220

Working with Tables 222

Creating a table 222

Changing the look of a table 223

Navigating and selecting in a table 224

Adding new rows or columns 225

Deleting rows or columns 226

Moving a table 226

Removing duplicate rows from a table 227

Sorting and filtering a table 228

Working with the Total row 233

Using formulas within a table 235

Referencing data in a table 237

Converting a table to a list 241

Using Advanced Filtering 242

Setting up a criteria range 242

Applying an advanced filter 243

Clearing an advanced filter 245

Specifying Advanced Filter Criteria 245

Specifying a single criterion 245

Specifying multiple criteria 247

Specifying computed criteria 249

Using Database Functions 250

Inserting Subtotals 252

Chapter 10: Miscellaneous Calculations 257

Unit Conversions 257

Rounding Numbers 261

Basic rounding formulas 262

Rounding to the nearest multiple 263

Rounding currency values 263

Working with fractional dollars 264

Using the INT and TRUNC functions 265

Rounding to an even or odd integer 266

Rounding to n significant digits 267

Solving Right Triangles 267

Area, Surface, Circumference, and Volume Calculations 270

Calculating the area and perimeter of a square 270

Calculating the area and perimeter of a rectangle 270

Calculating the area and perimeter of a circle 270

Calculating the area of a trapezoid 271

Calculating the area of a triangle 271

Calculating the surface and volume of a sphere 271

Calculating the surface and volume of a cube 271

Calculating the surface and volume of a rectangular solid 272

Calculating the surface and volume of a cone 272

Calculating the volume of a cylinder 272

Calculating the volume of a pyramid 273

Solving Simultaneous Equations 273

Working with Normal Distributions 274

Part III: Financial Formulas

Chapter 11: Borrowing and Investing Formulas 279

The Time Value of Money 279

Loan Calculations 280

Worksheet functions for calculating loan information 281

A loan calculation example 284

Credit card payments 285

Creating a loan amortization schedule 287

Calculating a loan with irregular payments 288

Investment Calculations 290

Future value of a single deposit 290

Present value of a series of payments 296

Future value of a series of deposits 296

Chapter 12: Discounting and Depreciation Formulas 299

Using the NPV Function 299

Definition of NPV 300

NPV function examples 301

Using the IRR Function 306

Rate of return 307

Geometric growth rates 308

Checking results 309

Irregular Cash Flows 310

Net present value 310

Internal rate of return 311

Depreciation Calculations 312

Chapter 13: Financial Schedules 317

Creating Financial Schedules 317

Creating Amortization Schedules 318

A simple amortization schedule 318

A dynamic amortization schedule 320

Credit card calculations 323

Summarizing Loan Options Using a Data Table 325

Creating a one-way data table 325

Creating a two-way data table 327

Financial Statements and Ratios 329

Basic financial statements 329

Ratio analysis 333

Creating Indices 337

Part IV: Array Formulas

Chapter 14: Introducing Arrays 341

Introducing Array Formulas 341

A multicell array formula 342

A single‐cell array formula 343

Creating an array constant 344

Array constant elements 345

Understanding the Dimensions of an Array 346

One‐dimensional horizontal arrays 346

One‐dimensional vertical arrays 347

Two‐dimensional arrays 347

Naming Array Constants 349

Working with Array Formulas 350

Entering an array formula 350

Selecting an array formula range 350

Editing an array formula 351

Expanding or contracting a multicell array formula 352

Using Multicell Array Formulas 353

Creating an array from values in a range 353

Creating an array constant from values in a range 353

Performing operations on an array 354

Using functions with an array 355

Transposing an array 355

Generating an array of consecutive integers 357

Using Single‐Cell Array Formulas 358

Counting characters in a range 358

Summing the three smallest values in a range 359

Counting text cells in a range 360

Eliminating intermediate formulas 362

Using an array in lieu of a range reference 364

Chapter 15: Performing Magic with Array Formulas 365

Working with Single‐Cell Array Formulas 365

Summing a range that contains errors 366

Counting the number of error values in a range 367

Summing the n largest values in a range 368

Computing an average that excludes zeros 368

Determining whether a particular value appears in a range 369

Counting the number of differences in two ranges 371

Returning the location of the maximum value in a range 372

Finding the row of a value’s nth occurrence in a range 373

Returning the longest text in a range 373

Determining whether a range contains valid values 374

Summing the digits of an integer 375

Summing rounded values 377

Summing every nth value in a range 377

Removing nonnumeric characters from a string 379

Determining the closest value in a range 380

Returning the last value in a column 380

Returning the last value in a row 381

Working with Multicell Array Formulas 382

Returning only positive values from a range 382

Returning nonblank cells from a range 384

Reversing the order of cells in a range 384

Sorting a range of values dynamically 385

Returning a list of unique items in a range 386

Displaying a calendar in a range 387

Part V: Miscellaneous Formula Techniques

Chapter 16: Importing and Cleaning Data 393

A Few Words About Data 393

Importing Data 394

Importing from a file 394

Importing a text file into a specified range 396

Copying and pasting data 398

Data Cleanup Techniques 398

Removing duplicate rows 398

Identifying duplicate rows 400

Splitting text 401

Changing the case of text 407

Removing extra spaces 408

Removing strange characters 409

Converting values 409

Classifying values 410

Joining columns 411

Rearranging columns 412

Randomizing the rows 412

Matching text in a list 413

Change vertical data to horizontal data 414

Filling gaps in an imported report 417

Spelling checking 418

Replacing or removing text in cells 419

Adding text to cells 420

Fixing trailing minus signs 420

A Data Cleaning Checklist 421

Exporting Data 422

Exporting to a text file 422

Exporting to other file formats 423

Chapter 17: Charting Techniques 425

Understanding the SERIES Formula 425

Using names in a SERIES formula 427

Unlinking a chart series from its data range 428

Creating Links to Cells 429

Adding a chart title link 429

Adding axis title links 430

Adding text links 430

Adding a linked picture to a chart 430

Chart Examples 431

Single data point charts 431

Displaying conditional colors in a column chart 433

Creating a comparative histogram 434

Creating a Gantt chart 435

Creating a box plot 438

Plotting every nth data point 439

Identifying maximum and minimum values in a chart 441

Creating a Timeline 442

Plotting mathematical functions 443

Plotting a circle 448

Creating a clock chart 450

Creating awesome designs 452

Working with Trendlines 453

Linear trendlines 454

Working with nonlinear trendlines 460

Summary of trendline equations 461

Creating Interactive Charts 462

Selecting a series from a drop‐down list 462

Plotting the last n data points 463

Choosing a start date and number of points 464

Displaying population data 465

Displaying weather data 465

Chapter 18: Pivot Tables 469

About Pivot Tables 469

A Pivot Table Example 470

Data Appropriate for a Pivot Table 472

Creating a Pivot Table Automatically 475

Creating a Pivot Table Manually 477

Specifying the data 477

Specifying the location for the pivot table 478

Laying out the pivot table 480

Formatting the pivot table 481

Modifying the pivot table 483

More Pivot Table Examples 485

Question 1 485

Question 2 486

Question 3 487

Question 4 487

Question 5 488

Question 6 489

Question 7 490

Grouping Pivot Table Items 491

A manual grouping example 491

Viewing grouped data 493

Automatic grouping examples 494

Creating a Frequency Distribution 498

Creating a Calculated Field or Calculated Item 499

Creating a calculated field 501

Inserting a calculated item 503

Filtering Pivot Tables with Slicers 506

Filtering Pivot Tables with a Timeline 507

Referencing Cells Within a Pivot Table 508

Another Pivot Table Example 510

Using the Data Model 513

Creating Pivot Charts 516

A pivot chart example 517

More about pivot charts 519

Chapter 19: Conditional Formatting 521

About Conditional Formatting 521

Specifying Conditional Formatting 523

Formatting types you can apply 523

Making your own rules 524

Conditional Formats That Use Graphics 525

Using data bars 525

Using color scales 527

Using icon sets 530

Creating Formula-Based Rules 533

Understanding relative and absolute references 534

Conditional formatting formula examples 536

Working with Conditional Formats 543

Managing rules 544

Copying cells that contain conditional formatting 544

Deleting conditional formatting 545

Locating cells that contain conditional formatting 545

Chapter 20: Using Data Validation 547

About Data Validation 547

Specifying Validation Criteria 548

Types of Validation Criteria You Can Apply 549

Creating a Drop‐Down List 551

Using Formulas for Data Validation Rules 552

Understanding Cell References 552

Data Validation Formula Examples 554

Accepting text only 554

Accepting a larger value than the previous cell 554

Accepting nonduplicate entries only 554

Accepting text that begins with a specific character 555

Accepting dates by the day of the week 556

Accepting only values that don’t exceed a total 556

Creating a dependent list 557

Using Structured Table Referencing 558

Chapter 21: Creating Megaformulas 561

What Is a Megaformula? 561

Creating a Megaformula: A Simple Example 562

Megaformula Examples 564

Using a megaformula to remove middle names 564

Using a megaformula to return a string’s last space character position 569

Using a megaformula to determine the validity of a credit card number 573

Using Intermediate Named Formulas 578

Generating random names 579

The Pros and Cons of Megaformulas 580

Chapter 22: Tools and Methods for Debugging Formulas 581

Formula Debugging? 581

Formula Problems and Solutions 582

Mismatched parentheses 583

Cells are filled with hash marks 584

Blank cells are not blank 584

Extra space characters 585

Formulas returning an error 585

Absolute/relative reference problems 590

Operator precedence problems 591

Formulas are not calculated 592

Actual versus displayed values 592

Floating‐point number errors 593

Phantom link errors 594

Logical value errors 595

Circular reference errors 596

Excel’s Auditing Tools 596

Identifying cells of a particular type 596

Viewing formulas 597

Tracing cell relationships 598

Tracing error values 600

Fixing circular reference errors 600

Using background error checking 600

Using Excel’s Formula Evaluator 603

Part VI: Developing Custom Worksheet Functions

Chapter 23: Introducing VBA 607

Fundamental Macro Concepts 607

Activating the Developer tab 608

Recording a macro 608

Understanding macro-enabled extensions 611

Macro security in Excel 611

Trusted locations 611

Storing macros in your Personal Macro Workbook 612

Assigning a macro to a button and other form controls 612

Placing a macro on the Quick Access toolbar 614

Working in the Visual Basic Editor 615

Understanding VBE components 615

Working with the Project window 616

Working with a code window 619

Customizing the VBA environment 622

Chapter 24: VBA Programming Concepts 627

A Brief Overview of the Excel Object Model 627

Understanding objects 628

Understanding collections 628

Understanding properties 629

Understanding methods 629

A brief look at variables 630

Error handling 633

Using code comments 636

An Introductory Example Function Procedure 636

Using Built-In VBA Functions 638

Controlling Execution 640

The If-Then construct 640

The Select Case construct 642

Looping blocks of instructions 643

Using Ranges 648

The For Each-Next construct 648

Referencing a range 649

Some useful properties of ranges 651

The Set keyword 655

The Intersect function 655

The Union function 656

The UsedRange property 656

Chapter 25: Function Procedure Basics 659

Why Create Custom Functions? 659

An Introductory VBA Function Example 660

About Function Procedures 662

Declaring a function 662

Choosing a name for your function 663

Using functions in formulas 664

Using function arguments 665

Using the Insert Function Dialog Box 665

Adding a function description 666

Specifying a function category 667

Adding argument descriptions 669

Testing and Debugging Your Functions 670

Using the VBA MsgBox statement 671

Using Debug.Print statements in your code 673

Calling the function from a Sub procedure 673

Setting a breakpoint in the function 676

Creating Add-Ins for Functions 676

Chapter 26: VBA Custom Function Examples 679

Simple Functions 679

Is the cell hidden? 680

Returning a worksheet name 680

Returning a workbook name 681

Returning the application’s name 681

Returning Excel’s version number 682

Returning cell formatting information 682

Determining a Cell’s Data Type 684

A Multifunctional Function 685

Generating Random Numbers 688

Generating random numbers that don’t change 688

Selecting a cell at random 690

Calculating Sales Commissions 691

A function for a simple commission structure 691

A function for a more complex commission structure 692

Text Manipulation Functions 693

Reversing a string 694

Scrambling text 694

Returning an acronym 695

Does the text match a pattern? 695

Does a cell contain a particular word? 696

Does a cell contain text? 698

Extracting the nth element from a string 698

Spelling out a number 699

Counting Functions 700

Counting pattern-matched cells 700

Counting sheets in a workbook 700

Counting words in a range 701

Date Functions 701

Calculating the next Monday 702

Calculating the next day of the week 702

Which week of the month? 703

Working with dates before 1900 703

Returning the Last Nonempty Cell in a Column or Row 704

The LASTINCOLUMN function 705

The LASTINROW function 705

Multisheet Functions 706

Returning the maximum value across all worksheets 706

The SHEETOFFSET function 708

Advanced Function Techniques 709

Returning an error value 709

Returning an array from a function 710

Returning an array of nonduplicated random integers 712

Randomizing a range 714

Using optional arguments 716

Using an indefinite number of arguments 717

Part VII: Appendixes

Appendix A: Excel Function Reference 725

Appendix B: Using Custom Number Formats 743

Index 763