Excel 2016 Formulas

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