Excel 2016 formulas

Content: 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