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 xxviiPart I: Understanding Formula BasicsChapter 1: The Excel User Interface in a Nutshell 3The Workings of Workbooks 3Worksheets 4Chart sheets 5Macro sheets and dialog sheets 5The Excel User Interface 5The Ribbon 6Backstage View 7Shortcut menus and the mini toolbar 7Dialog boxes 7Customizing the UI 8Task panes 9Customizing onscreen display 9Numeric formatting 9Stylistic formatting 9Protection Options 10Securing access to the entire workbook 10Limiting access to specific worksheet ranges 13Protecting the workbook structure 16Chapter 2: Basic Facts About Formulas 19Entering and Editing Formulas 19Formula elements 20Entering a formula 20Pasting names 22Spaces and line breaks 22Formula limits 23Sample formulas 23Editing formulas 24Using Operators in Formulas 25Reference operators 25Sample formulas that use operators 26Operator precedence 27Nested parentheses 29Calculating Formulas 30Cell and Range References 30Creating an absolute or a mixed reference 31Referencing other sheets or workbooks 33Copying or Moving Formulas 35Making an Exact Copy of a Formula 36Converting Formulas to Values 37Hiding Formulas 39Errors in Formulas 40Dealing with Circular References 41Goal Seeking 42A goal seeking example 42More about goal seeking 43Chapter 3: Working with Names 45What’s in a Name? 45A Name’s Scope 46Referencing names 47Referencing names from another workbook 48Conflicting names 48The Name Manager 48Creating names 49Editing names 50Deleting names 50Shortcuts for Creating Cell and Range Names 50The New Name dialog box 51Creating names using the Name box 52Creating names from text in cells 52Naming entire rows and columns 54Names created by Excel 55Creating Multisheet Names 55Working with Range and Cell Names 57Creating a list of names 58Using names in formulas 59Using the intersection operators with names 59Using the range operator with names 61Referencing a single cell in a multicell named range 61Applying names to existing formulas 62Applying names automatically when creating a formula 63Unapplying names 63Names with errors 64Viewing named ranges 64Using names in charts 64How Excel Maintains Cell and Range Names 65Inserting a row or column 65Deleting a row or a column 65Cutting and pasting 65Potential Problems with Names 66Name problems when copying sheets 66Name problems when deleting sheets 66The Secret to Understanding Names 68Naming constants 68Naming text constants 69Using worksheet functions in named formulas 70Using cell and range references in named formulas 71Using named formulas with relative references 72Advanced Techniques That Use Names 75Using the INDIRECT function with a named range 75Using arrays in named formulas 77Creating a dynamic named formula 78Using an XLM macro in a named formula 80Part II: Leveraging Excel FunctionsChapter 4: Introducing Worksheet Functions 85What Is a Function? 85Simplify your formulas 86Perform otherwise impossible calculations 86Speed up editing tasks 86Provide decision-making capability 87More about functions 87Function Argument Types 88Names as arguments 89Full-column or full-row as arguments 89Literal values as arguments 90Expressions as arguments 90Other functions as arguments 91Arrays as arguments 91Ways to Enter a Function into a Formula 91Entering a function manually 91Using the Function Library commands 93Using the Insert Function dialog box 94More tips for entering functions 96Chapter 5: Manipulating Text 99A Few Words About Text 99How many characters in a cell? 99Numbers as text 99Text Functions 101Determining whether a cell contains text 101Working with character codes 102Determining whether two strings are identical 105Joining two or more cells 105Displaying formatted values as text 106Displaying formatted currency values as text 108Removing excess spaces and nonprinting characters 108Counting characters in a string 109Repeating a character or string 109Creating a text histogram 110Padding a number 111Changing the case of text 112Extracting characters from a string 113Replacing text with other text 113Finding and searching within a string 114Searching and replacing within a string 115Advanced Text Formulas 115Counting specific characters in a cell 116Counting the occurrences of a substring in a cell 116Removing trailing minus signs 116Expressing a number as an ordinal 117Determining a column letter for a column number 118Extracting a filename from a path specification 118Extracting the first word of a string 119Extracting the last word of a string 119Extracting all but the first word of a string 120Extracting first names, middle names, and last names 120Removing titles from names 122Counting the number of words in a cell 122Chapter 6: Working with Dates and Times 125How Excel Handles Dates and Times 125Understanding date serial numbers 126Entering dates 127Understanding time serial numbers 129Entering times 130Formatting dates and times 131Problems with dates 133Date-Related Functions 134Displaying the current date 135Displaying any date with a function 136Generating a series of dates 137Converting a nondate string to a date 138Calculating the number of days between two dates 139Calculating the number of work days between two dates 139Offsetting a date using only work days 141Calculating the number of years between two dates 141Calculating a person’s age 142Determining the day of the year 143Determining the day of the week 144Determining the week of the year 144Determining the date of the most recent Sunday 144Determining the first day of the week after a date 145Determining the nth occurrence of a day of the week in a month 145Counting the occurrences of a day of the week 146Expressing a date as an ordinal number 147Calculating dates of holidays 147Determining the last day of a month 150Determining whether a year is a leap year 151Determining a date’s quarter 151Converting a year to roman numerals 151Time-Related Functions 152Displaying the current time 152Displaying any time using a function 153Calculating the difference between two times 154Summing times that exceed 24 hours 155Converting from military time 157Converting decimal hours, minutes, or seconds to a time 158Adding hours, minutes, or seconds to a time 158Converting between time zones 159Rounding time values 160Calculating Durations 161Chapter 7: Counting and Summing Techniques 163Counting and Summing Worksheet Cells 163Other Counting Methods 165Basic Counting Formulas 165Counting the total number of cells 166Counting blank cells 166Counting nonblank cells 167Counting numeric cells 167Counting text cells 168Counting nontext cells 168Counting logical values 168Counting error values in a range 168Advanced Counting Formulas 169Counting cells with the COUNTIF function 169Counting cells that meet multiple criteria 170Counting the most frequently occurring entry 173Counting the occurrences of specific text 174Counting the number of unique values 176Creating a frequency distribution 178Summing Formulas 184Summing all cells in a range 184Summing a range that contains errors 185Computing a cumulative sum 186Summing the “top n” values 187Conditional Sums Using a Single Criterion 188Summing only negative values 189Summing values based on a different range 190Summing values based on a text comparison 190Summing values based on a date comparison 190Conditional Sums Using Multiple Criteria 191Using And criteria 191Using Or criteria 192Using And and Or criteria 193Chapter 8: Using Lookup Functions 195What Is a Lookup Formula? 195Functions Relevant to Lookups 196Basic Lookup Formulas 198The VLOOKUP function 198The HLOOKUP function 200The LOOKUP function 201Combining the MATCH and INDEX functions 202Specialized Lookup Formulas 203Looking up an exact value 204Looking up a value to the left 206Performing a case-sensitive lookup 207Choosing among multiple lookup tables 207Determining letter grades for test scores 208Calculating a grade point average 209Performing a two-way lookup 211Performing a two-column lookup 212Determining the address of a value within a range 213Looking up a value by using the closest match 214Looking up a value using linear interpolation 215Chapter 9: Working with Tables and Lists 219Tables and Terminology 219A list example 220A table example 220Working with Tables 222Creating a table 222Changing the look of a table 223Navigating and selecting in a table 224Adding new rows or columns 225Deleting rows or columns 226Moving a table 226Removing duplicate rows from a table 227Sorting and filtering a table 228Working with the Total row 233Using formulas within a table 235Referencing data in a table 237Converting a table to a list 241Using Advanced Filtering 242Setting up a criteria range 242Applying an advanced filter 243Clearing an advanced filter 245Specifying Advanced Filter Criteria 245Specifying a single criterion 245Specifying multiple criteria 247Specifying computed criteria 249Using Database Functions 250Inserting Subtotals 252Chapter 10: Miscellaneous Calculations 257Unit Conversions 257Rounding Numbers 261Basic rounding formulas 262Rounding to the nearest multiple 263Rounding currency values 263Working with fractional dollars 264Using the INT and TRUNC functions 265Rounding to an even or odd integer 266Rounding to n significant digits 267Solving Right Triangles 267Area, Surface, Circumference, and Volume Calculations 270Calculating the area and perimeter of a square 270Calculating the area and perimeter of a rectangle 270Calculating the area and perimeter of a circle 270Calculating the area of a trapezoid 271Calculating the area of a triangle 271Calculating the surface and volume of a sphere 271Calculating the surface and volume of a cube 271Calculating the surface and volume of a rectangular solid 272Calculating the surface and volume of a cone 272Calculating the volume of a cylinder 272Calculating the volume of a pyramid 273Solving Simultaneous Equations 273Working with Normal Distributions 274Part III: Financial FormulasChapter 11: Borrowing and Investing Formulas 279The Time Value of Money 279Loan Calculations 280Worksheet functions for calculating loan information 281A loan calculation example 284Credit card payments 285Creating a loan amortization schedule 287Calculating a loan with irregular payments 288Investment Calculations 290Future value of a single deposit 290Present value of a series of payments 296Future value of a series of deposits 296Chapter 12: Discounting and Depreciation Formulas 299Using the NPV Function 299Definition of NPV 300NPV function examples 301Using the IRR Function 306Rate of return 307Geometric growth rates 308Checking results 309Irregular Cash Flows 310Net present value 310Internal rate of return 311Depreciation Calculations 312Chapter 13: Financial Schedules 317Creating Financial Schedules 317Creating Amortization Schedules 318A simple amortization schedule 318A dynamic amortization schedule 320Credit card calculations 323Summarizing Loan Options Using a Data Table 325Creating a one-way data table 325Creating a two-way data table 327Financial Statements and Ratios 329Basic financial statements 329Ratio analysis 333Creating Indices 337Part IV: Array FormulasChapter 14: Introducing Arrays 341Introducing Array Formulas 341A multicell array formula 342A single‐cell array formula 343Creating an array constant 344Array constant elements 345Understanding the Dimensions of an Array 346One‐dimensional horizontal arrays 346One‐dimensional vertical arrays 347Two‐dimensional arrays 347Naming Array Constants 349Working with Array Formulas 350Entering an array formula 350Selecting an array formula range 350Editing an array formula 351Expanding or contracting a multicell array formula 352Using Multicell Array Formulas 353Creating an array from values in a range 353Creating an array constant from values in a range 353Performing operations on an array 354Using functions with an array 355Transposing an array 355Generating an array of consecutive integers 357Using Single‐Cell Array Formulas 358Counting characters in a range 358Summing the three smallest values in a range 359Counting text cells in a range 360Eliminating intermediate formulas 362Using an array in lieu of a range reference 364Chapter 15: Performing Magic with Array Formulas 365Working with Single‐Cell Array Formulas 365Summing a range that contains errors 366Counting the number of error values in a range 367Summing the n largest values in a range 368Computing an average that excludes zeros 368Determining whether a particular value appears in a range 369Counting the number of differences in two ranges 371Returning the location of the maximum value in a range 372Finding the row of a value’s nth occurrence in a range 373Returning the longest text in a range 373Determining whether a range contains valid values 374Summing the digits of an integer 375Summing rounded values 377Summing every nth value in a range 377Removing nonnumeric characters from a string 379Determining the closest value in a range 380Returning the last value in a column 380Returning the last value in a row 381Working with Multicell Array Formulas 382Returning only positive values from a range 382Returning nonblank cells from a range 384Reversing the order of cells in a range 384Sorting a range of values dynamically 385Returning a list of unique items in a range 386Displaying a calendar in a range 387Part V: Miscellaneous Formula TechniquesChapter 16: Importing and Cleaning Data 393A Few Words About Data 393Importing Data 394Importing from a file 394Importing a text file into a specified range 396Copying and pasting data 398Data Cleanup Techniques 398Removing duplicate rows 398Identifying duplicate rows 400Splitting text 401Changing the case of text 407Removing extra spaces 408Removing strange characters 409Converting values 409Classifying values 410Joining columns 411Rearranging columns 412Randomizing the rows 412Matching text in a list 413Change vertical data to horizontal data 414Filling gaps in an imported report 417Spelling checking 418Replacing or removing text in cells 419Adding text to cells 420Fixing trailing minus signs 420A Data Cleaning Checklist 421Exporting Data 422Exporting to a text file 422Exporting to other file formats 423Chapter 17: Charting Techniques 425Understanding the SERIES Formula 425Using names in a SERIES formula 427Unlinking a chart series from its data range 428Creating Links to Cells 429Adding a chart title link 429Adding axis title links 430Adding text links 430Adding a linked picture to a chart 430Chart Examples 431Single data point charts 431Displaying conditional colors in a column chart 433Creating a comparative histogram 434Creating a Gantt chart 435Creating a box plot 438Plotting every nth data point 439Identifying maximum and minimum values in a chart 441Creating a Timeline 442Plotting mathematical functions 443Plotting a circle 448Creating a clock chart 450Creating awesome designs 452Working with Trendlines 453Linear trendlines 454Working with nonlinear trendlines 460Summary of trendline equations 461Creating Interactive Charts 462Selecting a series from a drop‐down list 462Plotting the last n data points 463Choosing a start date and number of points 464Displaying population data 465Displaying weather data 465Chapter 18: Pivot Tables 469About Pivot Tables 469A Pivot Table Example 470Data Appropriate for a Pivot Table 472Creating a Pivot Table Automatically 475Creating a Pivot Table Manually 477Specifying the data 477Specifying the location for the pivot table 478Laying out the pivot table 480Formatting the pivot table 481Modifying the pivot table 483More Pivot Table Examples 485Question 1 485Question 2 486Question 3 487Question 4 487Question 5 488Question 6 489Question 7 490Grouping Pivot Table Items 491A manual grouping example 491Viewing grouped data 493Automatic grouping examples 494Creating a Frequency Distribution 498Creating a Calculated Field or Calculated Item 499Creating a calculated field 501Inserting a calculated item 503Filtering Pivot Tables with Slicers 506Filtering Pivot Tables with a Timeline 507Referencing Cells Within a Pivot Table 508Another Pivot Table Example 510Using the Data Model 513Creating Pivot Charts 516A pivot chart example 517More about pivot charts 519Chapter 19: Conditional Formatting 521About Conditional Formatting 521Specifying Conditional Formatting 523Formatting types you can apply 523Making your own rules 524Conditional Formats That Use Graphics 525Using data bars 525Using color scales 527Using icon sets 530Creating Formula-Based Rules 533Understanding relative and absolute references 534Conditional formatting formula examples 536Working with Conditional Formats 543Managing rules 544Copying cells that contain conditional formatting 544Deleting conditional formatting 545Locating cells that contain conditional formatting 545Chapter 20: Using Data Validation 547About Data Validation 547Specifying Validation Criteria 548Types of Validation Criteria You Can Apply 549Creating a Drop‐Down List 551Using Formulas for Data Validation Rules 552Understanding Cell References 552Data Validation Formula Examples 554Accepting text only 554Accepting a larger value than the previous cell 554Accepting nonduplicate entries only 554Accepting text that begins with a specific character 555Accepting dates by the day of the week 556Accepting only values that don’t exceed a total 556Creating a dependent list 557Using Structured Table Referencing 558Chapter 21: Creating Megaformulas 561What Is a Megaformula? 561Creating a Megaformula: A Simple Example 562Megaformula Examples 564Using a megaformula to remove middle names 564Using a megaformula to return a string’s last space character position 569Using a megaformula to determine the validity of a credit card number 573Using Intermediate Named Formulas 578Generating random names 579The Pros and Cons of Megaformulas 580Chapter 22: Tools and Methods for Debugging Formulas 581Formula Debugging? 581Formula Problems and Solutions 582Mismatched parentheses 583Cells are filled with hash marks 584Blank cells are not blank 584Extra space characters 585Formulas returning an error 585Absolute/relative reference problems 590Operator precedence problems 591Formulas are not calculated 592Actual versus displayed values 592Floating‐point number errors 593Phantom link errors 594Logical value errors 595Circular reference errors 596Excel’s Auditing Tools 596Identifying cells of a particular type 596Viewing formulas 597Tracing cell relationships 598Tracing error values 600Fixing circular reference errors 600Using background error checking 600Using Excel’s Formula Evaluator 603Part VI: Developing Custom Worksheet FunctionsChapter 23: Introducing VBA 607Fundamental Macro Concepts 607Activating the Developer tab 608Recording a macro 608Understanding macro-enabled extensions 611Macro security in Excel 611Trusted locations 611Storing macros in your Personal Macro Workbook 612Assigning a macro to a button and other form controls 612Placing a macro on the Quick Access toolbar 614Working in the Visual Basic Editor 615Understanding VBE components 615Working with the Project window 616Working with a code window 619Customizing the VBA environment 622Chapter 24: VBA Programming Concepts 627A Brief Overview of the Excel Object Model 627Understanding objects 628Understanding collections 628Understanding properties 629Understanding methods 629A brief look at variables 630Error handling 633Using code comments 636An Introductory Example Function Procedure 636Using Built-In VBA Functions 638Controlling Execution 640The If-Then construct 640The Select Case construct 642Looping blocks of instructions 643Using Ranges 648The For Each-Next construct 648Referencing a range 649Some useful properties of ranges 651The Set keyword 655The Intersect function 655The Union function 656The UsedRange property 656Chapter 25: Function Procedure Basics 659Why Create Custom Functions? 659An Introductory VBA Function Example 660About Function Procedures 662Declaring a function 662Choosing a name for your function 663Using functions in formulas 664Using function arguments 665Using the Insert Function Dialog Box 665Adding a function description 666Specifying a function category 667Adding argument descriptions 669Testing and Debugging Your Functions 670Using the VBA MsgBox statement 671Using Debug.Print statements in your code 673Calling the function from a Sub procedure 673Setting a breakpoint in the function 676Creating Add-Ins for Functions 676Chapter 26: VBA Custom Function Examples 679Simple Functions 679Is the cell hidden? 680Returning a worksheet name 680Returning a workbook name 681Returning the application’s name 681Returning Excel’s version number 682Returning cell formatting information 682Determining a Cell’s Data Type 684A Multifunctional Function 685Generating Random Numbers 688Generating random numbers that don’t change 688Selecting a cell at random 690Calculating Sales Commissions 691A function for a simple commission structure 691A function for a more complex commission structure 692Text Manipulation Functions 693Reversing a string 694Scrambling text 694Returning an acronym 695Does the text match a pattern? 695Does a cell contain a particular word? 696Does a cell contain text? 698Extracting the nth element from a string 698Spelling out a number 699Counting Functions 700Counting pattern-matched cells 700Counting sheets in a workbook 700Counting words in a range 701Date Functions 701Calculating the next Monday 702Calculating the next day of the week 702Which week of the month? 703Working with dates before 1900 703Returning the Last Nonempty Cell in a Column or Row 704The LASTINCOLUMN function 705The LASTINROW function 705Multisheet Functions 706Returning the maximum value across all worksheets 706The SHEETOFFSET function 708Advanced Function Techniques 709Returning an error value 709Returning an array from a function 710Returning an array of nonduplicated random integers 712Randomizing a range 714Using optional arguments 716Using an indefinite number of arguments 717Part VII: AppendixesAppendix A: Excel Function Reference 725Appendix B: Using Custom Number Formats 743Index 763