Excel 2016 Formulas (Record no. 75287)
[ view plain ]
000 -LEADER | |
---|---|
fixed length control field | 18389nam a22001577a 4500 |
020 ## - INTERNATIONAL STANDARD BOOK NUMBER | |
ISBN | 9788126559879 |
082 ## - DEWEY DECIMAL CLASSIFICATION NUMBER | |
Classification number | 005.369 M58211 E |
Item number | 103944 |
100 ## - MAIN ENTRY--PERSONAL NAME | |
Personal name | Michael Alexander |
245 ## - TITLE STATEMENT | |
Title | Excel 2016 Formulas |
260 ## - PUBLICATION, DISTRIBUTION, ETC. | |
Place of publication | New Delhi |
Name of publisher | Wiley |
Date of publication | 2016 |
300 ## - PHYSICAL DESCRIPTION | |
Number of Pages | 782 |
500 ## - GENERAL NOTE | |
General note | Excel 2016 Formulas is fully updated to cover all of the tips, tricks, and techniques you need to maximize the power of Excel 2016 through the use of formulas |
505 ## - FORMATTED CONTENTS NOTE | |
Formatted contents note | Part I: Understanding Formula Basics<br/><br/>Chapter 1: The Excel User Interface in a Nutshell<br/><br/>The Workings of Workbooks<br/>Worksheets <br/>Chart sheets<br/>Macro sheets and dialog sheets <br/>The Excel User Interface<br/>The Ribbon<br/>Backstage View<br/>Shortcut menus and the mini toolbar<br/>Dialog boxes<br/>Customizing the UI<br/>Task panes<br/>Customizing onscreen display<br/>Numeric formatting<br/>Stylistic formatting<br/>Protection Options<br/>Securing access to the entire workbook<br/>Limiting access to specific worksheet ranges<br/>Protecting the workbook structure<br/> <br/><br/>Chapter 2: Basic Facts About Formulas<br/><br/>Entering and Editing Formulas<br/>Formula elements<br/>Entering a formula<br/>Pasting names<br/>Spaces and line breaks<br/>Formula limits<br/>Sample formulas<br/>Editing formulas<br/>Using Operators in Formulas<br/>Reference operators<br/>Sample formulas that use operators<br/>Operator precedence<br/>Nested parentheses<br/>Calculating Formulas<br/>Cell and Range References<br/>Creating an absolute or a mixed reference<br/>Referencing other sheets or workbooks<br/>Copying or Moving Formulas<br/>Making an Exact Copy of a Formula<br/>Converting Formulas to Values<br/>Hiding Formulas<br/>Errors in Formulas<br/>Dealing with Circular References<br/>Goal Seeking<br/>A goal seeking example<br/>More about goal seeking<br/> <br/><br/>Chapter 3: Working with Names<br/><br/>What's in a Name?<br/>A Name's Scope<br/>Referencing names<br/>Referencing names from another workbook<br/>Conflicting names<br/>The Name Manager<br/>Creating names<br/>Editing names <br/>Deleting names<br/>Shortcuts for Creating Cell and Range Names<br/>The New Name dialog box<br/>Creating names using the Name box<br/>Creating names from text in cells<br/>Naming entire rows and columns<br/>Names created by Excel<br/>Creating Multi sheet Names<br/>Working with Range and Cell Names<br/>Creating a list of names<br/>Using names in formulas<br/>Using the intersection operators with names<br/>Using the range operator with names<br/>Referencing a single cell in a multi cell named range<br/>Applying names to existing formulas<br/>Applying names automatically when creating a formula<br/>Unapplying names<br/>Names with errors<br/>Viewing named ranges<br/>Using names in charts<br/>How Excel Maintains Cell and Range Names<br/>Inserting a row or column<br/>Deleting a row or a column<br/>Cutting and pasting<br/>Potential Problems with Names<br/>Name problems when copying sheets<br/>Name problems when deleting sheets<br/>The Secret to Understanding Names<br/>Naming constants<br/>Naming text constants<br/>Using worksheet functions in named formulas<br/>Using cell and range references in named formulas<br/>Using named formulas with relative references<br/>Advanced Techniques That Use Names<br/>Using the INDIRECT function with a named range<br/>Using arrays in named formulas<br/>Creating a dynamic named formula<br/>Using an XLM macro in a named formula<br/> <br/><br/>Part II: Leveraging Excel Functions<br/><br/>Chapter 4: Introducing Worksheet Functions<br/><br/>What is a Function?<br/>Simplify your formulas<br/>Perform otherwise impossible calculations<br/>Speed up editing tasks<br/>Provide decision-making capability<br/>More about functions<br/>Function Argument Types<br/>Names as arguments<br/>Full-column or full-row as arguments<br/>Literal values as arguments<br/>Expressions as arguments<br/>Other functions as arguments<br/>Arrays as arguments<br/>Ways to Enter a Function into a Formula<br/>Entering a function manually<br/>Using the Function Library commands<br/>Using the Insert Function dialog box<br/>More tips for entering functions<br/> <br/><br/>Chapter 5: Manipulating Text<br/><br/>A Few Words About Text<br/>How many characters in a cell?<br/>Numbers as text<br/>Text Functions<br/>Determining whether a cell contains text<br/>Working with character codes<br/>Determining whether two strings are identical<br/>Joining two or more cells<br/>Displaying formatted values as text<br/>Displaying formatted currency values as text<br/>Removing excess spaces and nonprinting characters<br/>Counting characters in a string<br/>Repeating a character or string<br/>Creating a text histogram<br/>Padding a number<br/>Changing the case of text<br/>Extracting characters from a string<br/>Replacing text with other text<br/>Finding and searching within a string<br/>Searching and replacing within a string<br/>Advanced Text Formulas<br/>Counting specific characters in a cell<br/>Counting the occurrences of a substring in a cell<br/>Removing trailing minus signs<br/>Expressing a number as an ordinal<br/>Determining a column letter for a column number<br/>Extracting a filename from a path specification<br/>Extracting the first word of a string<br/>Extracting the last word of a string<br/>Extracting all but the first word of a string<br/>Extracting first names, middle names and last names<br/>Removing titles from names<br/>Counting the number of words in a cell<br/> <br/><br/>Chapter 6: Working with Dates and Times<br/><br/>How Excel Handles Dates and Times<br/>Understanding date serial numbers<br/>Entering dates<br/>Understanding time serial numbers<br/>Entering times<br/>Formatting dates and times<br/>Problems with dates<br/>Date-Related Functions<br/>Displaying the current date<br/>Displaying any date with a function<br/>Generating a series of dates<br/>Converting a nondate string to a date<br/>Calculating the number of days between two dates<br/>Calculating the number of work days between two dates<br/>Offsetting a date using only work days<br/>Calculating the number of years between two dates<br/>Calculating a person's age<br/>Determining the day of the year<br/>Determining the day of the week<br/>Determining the week of the year<br/>Determining the date of the most recent Sunday<br/>Determining the first day of the week after a date <br/>Determining the nth occurrence of a day of the week in a month<br/>Counting the occurrences of a day of the week<br/>Expressing a date as an ordinal number<br/>Calculating dates of holidays<br/>Determining the last day of a month<br/>Determining whether a year is a leap year<br/>Determining a date's quarter<br/>Converting a year to roman numerals<br/>Time-Related Functions<br/>Displaying the current time<br/>Displaying any time using a function<br/>Calculating the difference between two times<br/>Summing times that exceed 24 hours<br/>Converting from military time<br/>Converting decimal hours, minutes or seconds to a time<br/>Adding hours, minutes or seconds to a time<br/>Converting between time zones<br/>Rounding time values<br/>Calculating Durations<br/> <br/><br/>Chapter 7: Counting and Summing Techniques<br/><br/>Counting and Summing Worksheet Cells<br/>Other Counting Methods<br/>Basic Counting Formulas<br/>Counting the total number of cells<br/>Counting blank cells<br/>Counting nonblank cells<br/>Counting numeric cells<br/>Counting text cells<br/>Counting non text cells<br/>Counting logical values<br/>Counting error values in a range<br/>Advanced Counting Formulas<br/>Counting cells with the COUNTIF function<br/>Counting cells that meet multiple criteria<br/>Counting the most frequently occurring entry<br/>Counting the occurrences of specific text<br/>Counting the number of unique values<br/>Creating a frequency distribution<br/>Summing Formulas<br/>Summing all cells in a range<br/>Summing a range that contains errors<br/>Computing a cumulative sum<br/>Summing the "top n" values<br/>Conditional Sums Using a Single Criterion<br/>Summing only negative values<br/>Summing values based on a different range<br/>Summing values based on a text comparison<br/>Summing values based on a date comparison <br/>Conditional Sums Using Multiple Criteria<br/>Using And criteria<br/>Using or criteria<br/>Using And and Or criteria<br/> <br/><br/>Chapter 8: Using Lookup Functions<br/><br/>What Is a Lookup Formula?<br/>Functions Relevant to Lookups<br/>Basic Lookup Formulas<br/>The VLOOKUP function<br/>The HLOOKUP function<br/>The LOOKUP function<br/>Combining the MATCH and INDEX functions<br/>Specialized Lookup Formulas<br/>Looking up an exact value<br/>Looking up a value to the left<br/>Performing a case-sensitive lookup<br/>Choosing among multiple lookup tables<br/>Determining letter grades for test scores<br/>Calculating a grade point average<br/>Performing a two-way lookup<br/>Performing a two-column lookup<br/>Determining the address of a value within a range<br/>Looking up a value by using the closest match<br/>Looking up a value using linear interpolation<br/> <br/><br/>Chapter 9: Working with Tables and Lists<br/><br/>Tables and Terminology<br/>A list example<br/>A table example <br/>Working with Tables<br/>Creating a table <br/>Changing the look of a table<br/>Navigating and selecting in a table<br/>Adding new rows or columns<br/>Deleting rows or columns <br/>Moving a table<br/>Removing duplicate rows from a table<br/>Sorting and filtering a table<br/>Working with the Total row <br/>Using formulas within a table <br/>Referencing data in a table <br/>Converting a table to a list <br/>Using Advanced Filtering <br/>Setting up a criteria range <br/>Applying an advanced filter <br/>Clearing an advanced filter <br/>Specifying Advanced Filter Criteria<br/>Specifying a single criterion<br/>Specifying multiple criteria<br/>Specifying computed criteria<br/>Using Database Functions<br/>Inserting Subtotals<br/> <br/><br/>Chapter 10: Miscellaneous Calculations<br/><br/>Unit Conversions<br/>Rounding Numbers<br/>Basic rounding formulas<br/>Rounding to the nearest multiple<br/>Rounding currency values<br/>Working with fractional dollars<br/>Using the INT and TRUNC functions<br/>Rounding to an even or odd integer<br/>Rounding to n significant digits<br/>Solving Right Triangles<br/>Area, Surface, Circumference and Volume Calculations<br/>Calculating the area and perimeter of a square<br/>Calculating the area and perimeter of a rectangle<br/>Calculating the area and perimeter of a circle<br/>Calculating the area of a trapezoid<br/>Calculating the area of a triangle<br/>Calculating the surface and volume of a sphere<br/>Calculating the surface and volume of a cube<br/>Calculating the surface and volume of a rectangular solid<br/>Calculating the surface and volume of a cone<br/>Calculating the volume of a cylinder<br/>Calculating the volume of a pyramid<br/>Solving Simultaneous Equations<br/>Working with Normal Distributions<br/> <br/><br/>Part III: Financial Formulas<br/><br/>Chapter 11: Borrowing and Investing Formulas<br/><br/>The Time Value of Money<br/>Loan Calculations<br/>Worksheet functions for calculating loan information<br/>A loan calculation example<br/>Credit card payments<br/>Creating a loan amortization schedule<br/>Calculating a loan with irregular payments<br/>Investment Calculations<br/>Future value of a single deposit<br/>Present value of a series of payments<br/>Future value of a series of deposits<br/> <br/><br/>Chapter 12: Discounting and Depreciation Formulas<br/><br/>Using the NPV Function<br/>Definition of NPV<br/>NPV function examples<br/>Using the IRR Function<br/>Rate of return<br/>Geometric growth rates<br/>Checking results<br/>Irregular Cash Flows<br/>Net present value<br/>Internal rate of return<br/>Depreciation Calculations<br/> <br/><br/>Chapter 13: Financial Schedules<br/><br/>Creating Financial Schedules<br/>Creating Amortization Schedules<br/>A simple amortization schedule<br/>A dynamic amortization schedule<br/>Credit card calculations<br/>Summarizing Loan Options Using a Data Table<br/>Creating a one-way data table<br/>Creating a two-way data table<br/>Financial Statements and Ratios<br/>Basic financial statements<br/>Ratio analysis<br/>Creating Indices<br/> <br/><br/>Part IV: Array Formulas<br/><br/>Chapter 14: Introducing Arrays<br/><br/>Introducing Array Formulas<br/>A multi cell array formula<br/>A single cell array formula<br/>Creating an array constant<br/>Array constant elements<br/>Understanding the Dimensions of an Array<br/>One dimensional horizontal arrays<br/>One dimensional vertical arrays<br/>Two dimensional arrays<br/>Naming Array Constants<br/>Working with Array Formulas<br/>Entering an array formula<br/>Selecting an array formula range<br/>Editing an array formula<br/>Expanding or contracting a multi cell array formula<br/>Using Multi cell Array Formulas<br/>Creating an array from values in a range<br/>Creating an array constant from values in a range<br/>Performing operations on an array<br/>Using functions with an array<br/>Transposing an array<br/>Generating an array of consecutive integers<br/>Using Single Cell Array Formulas<br/>Counting characters in a range<br/>Summing the three smallest values in a range<br/>Counting text cells in a range<br/>Eliminating intermediate formulas<br/>Using an array in lieu of a range reference<br/> <br/><br/>Chapter 15: Performing Magic with Array Formulas<br/><br/>Working with Single Cell Array Formulas<br/>Summing a range that contains errors<br/>Counting the number of error values in a range<br/>Summing the n largest values in a range<br/>Computing an average that excludes zeros<br/>Determining whether a particular value appears in a range<br/>Counting the number of differences in two ranges<br/>Returning the location of the maximum value in a range<br/>Finding the row of a value's nth occurrence in a range<br/>Returning the longest text in a range<br/>Determining whether a range contains valid values<br/>Summing the digits of an integer<br/>Summing rounded values<br/>Summing every nth value in a range<br/>Removing nonnumeric characters from a string<br/>Determining the closest value in a range<br/>Returning the last value in a column<br/>Returning the last value in a row<br/>Working with Multi cell Array Formulas<br/>Returning only positive values from a range<br/>Returning nonblank cells from a range<br/>Reversing the order of cells in a range<br/>Sorting a range of values dynamically<br/>Returning a list of unique items in a range<br/>Displaying a calendar in a range<br/> <br/><br/>Part V: Miscellaneous Formula Techniques<br/><br/>Chapter 16: Importing and Cleaning Data<br/><br/>A Few Words About Data<br/>Importing Data<br/>Importing from a file<br/>Importing a text file into a specified range<br/>Copying and pasting data<br/>Data Cleanup Techniques<br/>Removing duplicate rows<br/>Identifying duplicate rows<br/>Splitting text<br/>Changing the case of text<br/>Removing extra spaces<br/>Removing strange characters<br/>Converting values<br/>Classifying values<br/>Joining columns<br/>Rearranging columns<br/>Randomizing the rows<br/>Matching text in a list<br/>Change vertical data to horizontal data<br/>Filling gaps in an imported report<br/>Spelling checking<br/>Replacing or removing text in cells<br/>Adding text to cells<br/>Fixing trailing minus signs<br/>A Data Cleaning Checklist<br/>Exporting Data<br/>Exporting to a text file<br/>Exporting to other file formats<br/> <br/><br/>Chapter 17: Charting Techniques<br/><br/>Understanding the SERIES Formula<br/>Using names in a SERIES formula<br/>Unlinking a chart series from its data range<br/>Creating Links to Cells<br/>Adding a chart title link<br/>Adding axis title links<br/>Adding text links<br/>Adding a linked picture to a chart<br/>Chart Examples<br/>Single data point charts<br/>Displaying conditional colors in a column chart<br/>Creating a comparative histogram<br/>Creating a Gantt chart<br/>Creating a box plot<br/>Plotting every nth data point<br/>Identifying maximum and minimum values in a chart<br/>Creating a Timeline<br/>Plotting mathematical functions<br/>Plotting a circle<br/>Creating a clock chart<br/>Creating awesome designs<br/>Working with trendlines<br/>Linear trendlines<br/>Working with nonlinear trendlines<br/>Summary of trendline equations<br/>Creating Interactive Charts<br/>Selecting a series from a drop down list<br/>Plotting the last n data points<br/>Choosing a start date and number of points<br/>Displaying population data<br/>Displaying weather data<br/> <br/><br/>Chapter 18: Pivot Tables<br/><br/>About Pivot Tables<br/>A Pivot Table Example<br/>Data Appropriate for a Pivot Table<br/>Creating a Pivot Table Automatically<br/>Creating a Pivot Table Manually<br/>Specifying the data<br/>Specifying the location for the pivot table<br/>Laying out the pivot table<br/>Formatting the pivot table<br/>Modifying the pivot table<br/>More Pivot Table Examples<br/>Question 1<br/>Question 2<br/>Question 3<br/>Question 4<br/>Question 5<br/>Question 6<br/>Question 7<br/>Grouping Pivot Table Items<br/>A manual grouping example<br/>Viewing grouped data<br/>Automatic grouping examples<br/>Creating a Frequency Distribution<br/>Creating a Calculated Field or Calculated Item<br/>Creating a calculated field<br/>Inserting a calculated item<br/>Filtering Pivot Tables with Slicers<br/>Filtering Pivot Tables with a Timeline<br/>Referencing Cells Within a Pivot Table<br/>Another Pivot Table Example<br/>Using the Data Model<br/>Creating Pivot Charts<br/>A pivot chart example<br/>More about pivot charts<br/> <br/><br/>Chapter 19: Conditional Formatting<br/><br/>About Conditional Formatting<br/>Specifying Conditional Formatting<br/>Formatting types you can apply<br/>Making your own rules<br/>Conditional Formats That Use Graphics<br/>Using data bars<br/>Using color scales<br/>Using icon sets<br/>Creating Formula-Based Rules<br/>Understanding relative and absolute references<br/>Conditional formatting formula examples<br/>Working with Conditional Formats<br/>Managing rules<br/>Copying cells that contain conditional formatting<br/>Deleting conditional formatting<br/>Locating cells that contain conditional formatting<br/> <br/><br/>Chapter 20: Using Data Validation<br/><br/>About Data Validation<br/>Specifying Validation Criteria<br/>Types of Validation Criteria You Can Apply<br/>Creating a Drop Down List<br/>Using Formulas for Data Validation Rules<br/>Understanding Cell References<br/>Data Validation Formula Examples<br/>Accepting text only<br/>Accepting a larger value than the previous cell<br/>Accepting non duplicate entries only<br/>Accepting text that begins with a specific character<br/>Accepting dates by the day of the week<br/>Accepting only values that don't exceed a total<br/>Creating a dependent list<br/>Using Structured Table Referencing<br/> <br/><br/>Chapter 21: Creating Megaformulas<br/><br/>What Is a Megaformula?<br/>Creating a Megaformula: A Simple Example<br/>Megaformula Examples<br/>Using a megaformula to remove middle names<br/>Using a megaformula to return a string's last space character position<br/>Using a megaformula to determine the validity of a credit card number<br/>Using Intermediate Named Formulas<br/>Generating random names<br/>The Pros and Cons of Megaformulas<br/> <br/><br/>Chapter 22: Tools and Methods for Debugging Formulas<br/><br/>Formula Debugging?<br/>Formula Problems and Solutions<br/>Mismatched parentheses<br/>Cells are filled with hash marks<br/>Blank cells are not blank<br/>Extra space characters<br/>Formulas returning an error<br/>Absolute / relative reference problems<br/>Operator precedence problems<br/>Formulas are not calculated<br/>Actual versus displayed values<br/>FloatingÂ]point number errors<br/>Phantom link errors<br/>Logical value errors<br/>Circular reference errors<br/>Excel's Auditing Tools<br/>Identifying cells of a particular type<br/>Viewing formulas<br/>Tracing cell relationships<br/>Tracing error values<br/>Fixing circular reference errors<br/>Using background error checking<br/>Using Excel's Formula Evaluator<br/> <br/><br/>Part VI: Developing Custom Worksheet Functions<br/><br/>Chapter 23: Introducing |
700 ## - ADDED ENTRY--PERSONAL NAME | |
Personal name | Dick Kusleika |
942 ## - ADDED ENTRY ELEMENTS (KOHA) | |
Koha item type | Books |
No items available.