Excel 2016 Formulas.
- 1st ed.
- 1 online resource (818 pages)
- Mr. Spreadsheet's Bookshelf Series .
- Mr. Spreadsheet's Bookshelf Series .
Intro -- ExcelŽ 2016 Formulas -- About the Authors -- About the Technical Editor -- Credits -- Contents at a Glance -- Table of Contents -- Introduction -- Part I: Understanding Formula Basics -- Chapter 1: The Excel User Interface in a Nutshell -- The Workings of Workbooks -- Worksheets -- Chart sheets -- Macro sheets and dialog sheets -- The Excel User Interface -- The Ribbon -- Backstage View -- Shortcut menus and the mini toolbar -- Dialog boxes -- Customizing the UI -- Task panes -- Customizing onscreen display -- Numeric formatting -- Stylistic formatting -- Protection Options -- Securing access to the entire workbook -- Limiting access to specific worksheet ranges -- Protecting the workbook structure -- Chapter 2: Basic Facts About Formulas -- Entering and Editing Formulas -- Formula elements -- Entering a formula -- Pasting names -- Spaces and line breaks -- Formula limits -- Sample formulas -- Editing formulas -- Using Operators in Formulas -- Reference operators -- Sample formulas that use operators -- Operator precedence -- Nested parentheses -- Calculating Formulas -- Cell and Range References -- Creating an absolute or a mixed reference -- Referencing other sheets or workbooks -- Copying or Moving Formulas -- Making an Exact Copy of a Formula -- Converting Formulas to Values -- Hiding Formulas -- Errors in Formulas -- Dealing with Circular References -- Goal Seeking -- A goal seeking example -- More about goal seeking -- Chapter 3: Working with Names -- What's in a Name? -- A Name's Scope -- Referencing names -- Referencing names from another workbook -- Conflicting names -- The Name Manager -- Creating names -- Editing names -- Deleting names -- Shortcuts for Creating Cell and Range Names -- The New Name dialog box -- Creating names using the Name box -- Creating names from text in cells -- Naming entire rows and columns. Names created by Excel -- Creating Multisheet Names -- Working with Range and Cell Names -- Creating a list of names -- Using names in formulas -- Using the intersection operators with names -- Using the range operator with names -- Referencing a single cell in a multicell named range -- Applying names to existing formulas -- Applying names automatically when creating a formula -- Unapplying names -- Names with errors -- Viewing named ranges -- Using names in charts -- How Excel Maintains Cell and Range Names -- Inserting a row or column -- Deleting a row or a column -- Cutting and pasting -- Potential Problems with Names -- Name problems when copying sheets -- Name problems when deleting sheets -- The Secret to Understanding Names -- Naming constants -- Naming text constants -- Using worksheet functions in named formulas -- Using cell and range references in named formulas -- Using named formulas with relative references -- Advanced Techniques That Use Names -- Using the INDIRECT function with a named range -- Using arrays in named formulas -- Creating a dynamic named formula -- Using an XLM macro in a named formula -- Part II: Leveraging Excel Functions -- Chapter 4: Introducing Worksheet Functions -- What Is a Function? -- Simplify your formulas -- Perform otherwise impossible calculations -- Speed up editing tasks -- Provide decision-making capability -- More about functions -- Function Argument Types -- Names as arguments -- Full-column or full-row as arguments -- Literal values as arguments -- Expressions as arguments -- Other functions as arguments -- Arrays as arguments -- Ways to Enter a Function into a Formula -- Entering a function manually -- Using the Function Library commands -- Using the Insert Function dialog box -- More tips for entering functions -- Chapter 5: Manipulating Text -- A Few Words About Text. How many characters in a cell? -- Numbers as text -- Text Functions -- Determining whether a cell contains text -- Working with character codes -- Determining whether two strings are identical -- Joining two or more cells -- Displaying formatted values as text -- Displaying formatted currency values as text -- Removing excess spaces and nonprinting characters -- Counting characters in a string -- Repeating a character or string -- Creating a text histogram -- Padding a number -- Changing the case of text -- Extracting characters from a string -- Replacing text with other text -- Finding and searching within a string -- Searching and replacing within a string -- Advanced Text Formulas -- Counting specific characters in a cell -- Counting the occurrences of a substring in a cell -- Removing trailing minus signs -- Expressing a number as an ordinal -- Determining a column letter for a column number -- Extracting a filename from a path specification -- Extracting the first word of a string -- Extracting the last word of a string -- Extracting all but the first word of a string -- Extracting first names, middle names, and last names -- Removing titles from names -- Counting the number of words in a cell -- Chapter 6: Working with Dates and Times -- How Excel Handles Dates and Times -- Understanding date serial numbers -- Entering dates -- Understanding time serial numbers -- Entering times -- Formatting dates and times -- Problems with dates -- Date-Related Functions -- Displaying the current date -- Displaying any date with a function -- Generating a series of dates -- Converting a nondate string to a date -- Calculating the number of days between two dates -- Calculating the number of work days between two dates -- Offsetting a date using only work days -- Calculating the number of years between two dates -- Calculating a person's age. Determining the day of the year -- Determining the day of the week -- Determining the week of the year -- Determining the date of the most recent Sunday -- Determining the first day of the week after a date -- Determining the nth occurrence of a day of the week in a month -- Counting the occurrences of a day of the week -- Expressing a date as an ordinal number -- Calculating dates of holidays -- Determining the last day of a month -- Determining whether a year is a leap year -- Determining a date's quarter -- Converting a year to roman numerals -- Time-Related Functions -- Displaying the current time -- Displaying any time using a function -- Calculating the difference between two times -- Summing times that exceed 24 hours -- Converting from military time -- Converting decimal hours, minutes, or seconds to a time -- Adding hours, minutes, or seconds to a time -- Converting between time zones -- Rounding time values -- Calculating Durations -- Chapter 7: Counting and Summing Techniques -- Counting and Summing Worksheet Cells -- Other Counting Methods -- Basic Counting Formulas -- Counting the total number of cells -- Counting blank cells -- Counting nonblank cells -- Counting numeric cells -- Counting text cells -- Counting nontext cells -- Counting logical values -- Counting error values in a range -- Advanced Counting Formulas -- Counting cells with the COUNTIF function -- Counting cells that meet multiple criteria -- Counting the most frequently occurring entry -- Counting the occurrences of specific text -- Counting the number of unique values -- Creating a frequency distribution -- Summing Formulas -- Summing all cells in a range -- Summing a range that contains errors -- Computing a cumulative sum -- Summing the "top n" values -- Conditional Sums Using a Single Criterion -- Summing only negative values. Summing values based on a different range -- Summing values based on a text comparison -- Summing values based on a date comparison -- Conditional Sums Using Multiple Criteria -- Using And criteria -- Using Or criteria -- Using And and Or criteria -- Chapter 8: Using Lookup Functions -- What Is a Lookup Formula? -- Functions Relevant to Lookups -- Basic Lookup Formulas -- The VLOOKUP function -- The HLOOKUP function -- The LOOKUP function -- Combining the MATCH and INDEX functions -- Specialized Lookup Formulas -- Looking up an exact value -- Looking up a value to the left -- Performing a case-sensitive lookup -- Choosing among multiple lookup tables -- Determining letter grades for test scores -- Calculating a grade point average -- Performing a two-way lookup -- Performing a two-column lookup -- Determining the address of a value within a range -- Looking up a value by using the closest match -- Looking up a value using linear interpolation -- Chapter 9: Working with Tables and Lists -- Tables and Terminology -- A list example -- A table example -- Working with Tables -- Creating a table -- Changing the look of a table -- Navigating and selecting in a table -- Adding new rows or columns -- Deleting rows or columns -- Moving a table -- Removing duplicate rows from a table -- Sorting and filtering a table -- Working with the Total row -- Using formulas within a table -- Referencing data in a table -- Converting a table to a list -- Using Advanced Filtering -- Setting up a criteria range -- Applying an advanced filter -- Clearing an advanced filter -- Specifying Advanced Filter Criteria -- Specifying a single criterion -- Specifying multiple criteria -- Specifying computed criteria -- Using Database Functions -- Inserting Subtotals -- Chapter 10: Miscellaneous Calculations -- Unit Conversions -- Rounding Numbers -- Basic rounding formulas. Rounding to the nearest multiple.