{"product_id":"excel-2016-formulas-isbn-9781119067863","title":"Excel 2016 Formulas","description":"\u003cb\u003eLeverage the full power of Excel formulas\u003c\/b\u003e \u003cp\u003e\u003ci\u003eExcel 2016 Formulas\u003c\/i\u003e 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. This comprehensive book explains how to create financial formulas, release the power of array formulas, develop custom worksheet functions with VBA, debug formulas, and much more. Whether you're a beginner, a power user, or somewhere in between this is your essential go-to for the latest on Excel formulas. \u003c\/p\u003e\u003cp\u003eWhen conducting simple math or building highly complicated spreadsheets that require formulas up to the task, leveraging the right formula can heighten the accuracy and efficiency of your work, and can improve the speed with which you compile and analyze data. Understanding which formulas to use and knowing how to create a formula when you need to are essential. \u003c\/p\u003e\u003cul\u003e \u003cli\u003eAccess tips, tricks, and techniques that have been fully updated to reflect the latest capabilities of Microsoft Excel\u003c\/li\u003e \u003cli\u003eCreate and use formulas that have the power to transform your Excel experience\u003c\/li\u003e \u003cli\u003eLeverage supplemental material online, including sample files, templates, and worksheets from the book\u003c\/li\u003e \u003c\/ul\u003e \u003cp\u003eIntroduction xxvii\u003c\/p\u003e \u003cp\u003e\u003cb\u003ePart I: Understanding Formula Basics\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 1: The Excel User Interface in a Nutshell 3\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eThe Workings of Workbooks 3\u003c\/p\u003e \u003cp\u003eWorksheets 4\u003c\/p\u003e \u003cp\u003eChart sheets 5\u003c\/p\u003e \u003cp\u003eMacro sheets and dialog sheets 5\u003c\/p\u003e \u003cp\u003eThe Excel User Interface 5\u003c\/p\u003e \u003cp\u003eThe Ribbon 6\u003c\/p\u003e \u003cp\u003eBackstage View 7\u003c\/p\u003e \u003cp\u003eShortcut menus and the mini toolbar 7\u003c\/p\u003e \u003cp\u003eDialog boxes 7\u003c\/p\u003e \u003cp\u003eCustomizing the UI 8\u003c\/p\u003e \u003cp\u003eTask panes 9\u003c\/p\u003e \u003cp\u003eCustomizing onscreen display 9\u003c\/p\u003e \u003cp\u003eNumeric formatting 9\u003c\/p\u003e \u003cp\u003eStylistic formatting 9\u003c\/p\u003e \u003cp\u003eProtection Options 10\u003c\/p\u003e \u003cp\u003eSecuring access to the entire workbook 10\u003c\/p\u003e \u003cp\u003eLimiting access to specific worksheet ranges 13\u003c\/p\u003e \u003cp\u003eProtecting the workbook structure 16\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 2: Basic Facts About Formulas 19\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eEntering and Editing Formulas 19\u003c\/p\u003e \u003cp\u003eFormula elements 20\u003c\/p\u003e \u003cp\u003eEntering a formula 20\u003c\/p\u003e \u003cp\u003ePasting names 22\u003c\/p\u003e \u003cp\u003eSpaces and line breaks 22\u003c\/p\u003e \u003cp\u003eFormula limits 23\u003c\/p\u003e \u003cp\u003eSample formulas 23\u003c\/p\u003e \u003cp\u003eEditing formulas 24\u003c\/p\u003e \u003cp\u003eUsing Operators in Formulas 25\u003c\/p\u003e \u003cp\u003eReference operators 25\u003c\/p\u003e \u003cp\u003eSample formulas that use operators 26\u003c\/p\u003e \u003cp\u003eOperator precedence 27\u003c\/p\u003e \u003cp\u003eNested parentheses 29\u003c\/p\u003e \u003cp\u003eCalculating Formulas 30\u003c\/p\u003e \u003cp\u003eCell and Range References 30\u003c\/p\u003e \u003cp\u003eCreating an absolute or a mixed reference 31\u003c\/p\u003e \u003cp\u003eReferencing other sheets or workbooks 33\u003c\/p\u003e \u003cp\u003eCopying or Moving Formulas 35\u003c\/p\u003e \u003cp\u003eMaking an Exact Copy of a Formula 36\u003c\/p\u003e \u003cp\u003eConverting Formulas to Values 37\u003c\/p\u003e \u003cp\u003eHiding Formulas 39\u003c\/p\u003e \u003cp\u003eErrors in Formulas 40\u003c\/p\u003e \u003cp\u003eDealing with Circular References 41\u003c\/p\u003e \u003cp\u003eGoal Seeking 42\u003c\/p\u003e \u003cp\u003eA goal seeking example 42\u003c\/p\u003e \u003cp\u003eMore about goal seeking 43\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 3: Working with Names 45\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eWhat’s in a Name? 45\u003c\/p\u003e \u003cp\u003eA Name’s Scope 46\u003c\/p\u003e \u003cp\u003eReferencing names 47\u003c\/p\u003e \u003cp\u003eReferencing names from another workbook 48\u003c\/p\u003e \u003cp\u003eConflicting names 48\u003c\/p\u003e \u003cp\u003eThe Name Manager 48\u003c\/p\u003e \u003cp\u003eCreating names 49\u003c\/p\u003e \u003cp\u003eEditing names 50\u003c\/p\u003e \u003cp\u003eDeleting names 50\u003c\/p\u003e \u003cp\u003eShortcuts for Creating Cell and Range Names 50\u003c\/p\u003e \u003cp\u003eThe New Name dialog box 51\u003c\/p\u003e \u003cp\u003eCreating names using the Name box 52\u003c\/p\u003e \u003cp\u003eCreating names from text in cells 52\u003c\/p\u003e \u003cp\u003eNaming entire rows and columns 54\u003c\/p\u003e \u003cp\u003eNames created by Excel 55\u003c\/p\u003e \u003cp\u003eCreating Multisheet Names 55\u003c\/p\u003e \u003cp\u003eWorking with Range and Cell Names 57\u003c\/p\u003e \u003cp\u003eCreating a list of names 58\u003c\/p\u003e \u003cp\u003eUsing names in formulas 59\u003c\/p\u003e \u003cp\u003eUsing the intersection operators with names 59\u003c\/p\u003e \u003cp\u003eUsing the range operator with names 61\u003c\/p\u003e \u003cp\u003eReferencing a single cell in a multicell named range 61\u003c\/p\u003e \u003cp\u003eApplying names to existing formulas 62\u003c\/p\u003e \u003cp\u003eApplying names automatically when creating a formula 63\u003c\/p\u003e \u003cp\u003eUnapplying names 63\u003c\/p\u003e \u003cp\u003eNames with errors 64\u003c\/p\u003e \u003cp\u003eViewing named ranges 64\u003c\/p\u003e \u003cp\u003eUsing names in charts 64\u003c\/p\u003e \u003cp\u003eHow Excel Maintains Cell and Range Names 65\u003c\/p\u003e \u003cp\u003eInserting a row or column 65\u003c\/p\u003e \u003cp\u003eDeleting a row or a column 65\u003c\/p\u003e \u003cp\u003eCutting and pasting 65\u003c\/p\u003e \u003cp\u003ePotential Problems with Names 66\u003c\/p\u003e \u003cp\u003eName problems when copying sheets 66\u003c\/p\u003e \u003cp\u003eName problems when deleting sheets 66\u003c\/p\u003e \u003cp\u003eThe Secret to Understanding Names 68\u003c\/p\u003e \u003cp\u003eNaming constants 68\u003c\/p\u003e \u003cp\u003eNaming text constants 69\u003c\/p\u003e \u003cp\u003eUsing worksheet functions in named formulas 70\u003c\/p\u003e \u003cp\u003eUsing cell and range references in named formulas 71\u003c\/p\u003e \u003cp\u003eUsing named formulas with relative references 72\u003c\/p\u003e \u003cp\u003eAdvanced Techniques That Use Names 75\u003c\/p\u003e \u003cp\u003eUsing the INDIRECT function with a named range 75\u003c\/p\u003e \u003cp\u003eUsing arrays in named formulas 77\u003c\/p\u003e \u003cp\u003eCreating a dynamic named formula 78\u003c\/p\u003e \u003cp\u003eUsing an XLM macro in a named formula 80\u003c\/p\u003e \u003cp\u003e\u003cb\u003ePart II: Leveraging Excel Functions\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 4: Introducing Worksheet Functions 85\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eWhat Is a Function? 85\u003c\/p\u003e \u003cp\u003eSimplify your formulas 86\u003c\/p\u003e \u003cp\u003ePerform otherwise impossible calculations 86\u003c\/p\u003e \u003cp\u003eSpeed up editing tasks 86\u003c\/p\u003e \u003cp\u003eProvide decision-making capability 87\u003c\/p\u003e \u003cp\u003eMore about functions 87\u003c\/p\u003e \u003cp\u003eFunction Argument Types 88\u003c\/p\u003e \u003cp\u003eNames as arguments 89\u003c\/p\u003e \u003cp\u003eFull-column or full-row as arguments 89\u003c\/p\u003e \u003cp\u003eLiteral values as arguments 90\u003c\/p\u003e \u003cp\u003eExpressions as arguments 90\u003c\/p\u003e \u003cp\u003eOther functions as arguments 91\u003c\/p\u003e \u003cp\u003eArrays as arguments 91\u003c\/p\u003e \u003cp\u003eWays to Enter a Function into a Formula 91\u003c\/p\u003e \u003cp\u003eEntering a function manually 91\u003c\/p\u003e \u003cp\u003eUsing the Function Library commands 93\u003c\/p\u003e \u003cp\u003eUsing the Insert Function dialog box 94\u003c\/p\u003e \u003cp\u003eMore tips for entering functions 96\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 5: Manipulating Text 99\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eA Few Words About Text 99\u003c\/p\u003e \u003cp\u003eHow many characters in a cell? 99\u003c\/p\u003e \u003cp\u003eNumbers as text 99\u003c\/p\u003e \u003cp\u003eText Functions 101\u003c\/p\u003e \u003cp\u003eDetermining whether a cell contains text 101\u003c\/p\u003e \u003cp\u003eWorking with character codes 102\u003c\/p\u003e \u003cp\u003eDetermining whether two strings are identical 105\u003c\/p\u003e \u003cp\u003eJoining two or more cells 105\u003c\/p\u003e \u003cp\u003eDisplaying formatted values as text 106\u003c\/p\u003e \u003cp\u003eDisplaying formatted currency values as text 108\u003c\/p\u003e \u003cp\u003eRemoving excess spaces and nonprinting characters 108\u003c\/p\u003e \u003cp\u003eCounting characters in a string 109\u003c\/p\u003e \u003cp\u003eRepeating a character or string 109\u003c\/p\u003e \u003cp\u003eCreating a text histogram 110\u003c\/p\u003e \u003cp\u003ePadding a number 111\u003c\/p\u003e \u003cp\u003eChanging the case of text 112\u003c\/p\u003e \u003cp\u003eExtracting characters from a string 113\u003c\/p\u003e \u003cp\u003eReplacing text with other text 113\u003c\/p\u003e \u003cp\u003eFinding and searching within a string 114\u003c\/p\u003e \u003cp\u003eSearching and replacing within a string 115\u003c\/p\u003e \u003cp\u003eAdvanced Text Formulas 115\u003c\/p\u003e \u003cp\u003eCounting specific characters in a cell 116\u003c\/p\u003e \u003cp\u003eCounting the occurrences of a substring in a cell 116\u003c\/p\u003e \u003cp\u003eRemoving trailing minus signs 116\u003c\/p\u003e \u003cp\u003eExpressing a number as an ordinal 117\u003c\/p\u003e \u003cp\u003eDetermining a column letter for a column number 118\u003c\/p\u003e \u003cp\u003eExtracting a filename from a path specification 118\u003c\/p\u003e \u003cp\u003eExtracting the first word of a string 119\u003c\/p\u003e \u003cp\u003eExtracting the last word of a string 119\u003c\/p\u003e \u003cp\u003eExtracting all but the first word of a string 120\u003c\/p\u003e \u003cp\u003eExtracting first names, middle names, and last names 120\u003c\/p\u003e \u003cp\u003eRemoving titles from names 122\u003c\/p\u003e \u003cp\u003eCounting the number of words in a cell 122\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 6: Working with Dates and Times 125\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eHow Excel Handles Dates and Times 125\u003c\/p\u003e \u003cp\u003eUnderstanding date serial numbers 126\u003c\/p\u003e \u003cp\u003eEntering dates 127\u003c\/p\u003e \u003cp\u003eUnderstanding time serial numbers 129\u003c\/p\u003e \u003cp\u003eEntering times 130\u003c\/p\u003e \u003cp\u003eFormatting dates and times 131\u003c\/p\u003e \u003cp\u003eProblems with dates 133\u003c\/p\u003e \u003cp\u003eDate-Related Functions 134\u003c\/p\u003e \u003cp\u003eDisplaying the current date 135\u003c\/p\u003e \u003cp\u003eDisplaying any date with a function 136\u003c\/p\u003e \u003cp\u003eGenerating a series of dates 137\u003c\/p\u003e \u003cp\u003eConverting a nondate string to a date 138\u003c\/p\u003e \u003cp\u003eCalculating the number of days between two dates 139\u003c\/p\u003e \u003cp\u003eCalculating the number of work days between two dates 139\u003c\/p\u003e \u003cp\u003eOffsetting a date using only work days 141\u003c\/p\u003e \u003cp\u003eCalculating the number of years between two dates 141\u003c\/p\u003e \u003cp\u003eCalculating a person’s age 142\u003c\/p\u003e \u003cp\u003eDetermining the day of the year 143\u003c\/p\u003e \u003cp\u003eDetermining the day of the week 144\u003c\/p\u003e \u003cp\u003eDetermining the week of the year 144\u003c\/p\u003e \u003cp\u003eDetermining the date of the most recent Sunday 144\u003c\/p\u003e \u003cp\u003eDetermining the first day of the week after a date 145\u003c\/p\u003e \u003cp\u003eDetermining the nth occurrence of a day of the week in a month 145\u003c\/p\u003e \u003cp\u003eCounting the occurrences of a day of the week 146\u003c\/p\u003e \u003cp\u003eExpressing a date as an ordinal number 147\u003c\/p\u003e \u003cp\u003eCalculating dates of holidays 147\u003c\/p\u003e \u003cp\u003eDetermining the last day of a month 150\u003c\/p\u003e \u003cp\u003eDetermining whether a year is a leap year 151\u003c\/p\u003e \u003cp\u003eDetermining a date’s quarter 151\u003c\/p\u003e \u003cp\u003eConverting a year to roman numerals 151\u003c\/p\u003e \u003cp\u003eTime-Related Functions 152\u003c\/p\u003e \u003cp\u003eDisplaying the current time 152\u003c\/p\u003e \u003cp\u003eDisplaying any time using a function 153\u003c\/p\u003e \u003cp\u003eCalculating the difference between two times 154\u003c\/p\u003e \u003cp\u003eSumming times that exceed 24 hours 155\u003c\/p\u003e \u003cp\u003eConverting from military time 157\u003c\/p\u003e \u003cp\u003eConverting decimal hours, minutes, or seconds to a time 158\u003c\/p\u003e \u003cp\u003eAdding hours, minutes, or seconds to a time 158\u003c\/p\u003e \u003cp\u003eConverting between time zones 159\u003c\/p\u003e \u003cp\u003eRounding time values 160\u003c\/p\u003e \u003cp\u003eCalculating Durations 161\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 7: Counting and Summing Techniques 163\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eCounting and Summing Worksheet Cells 163\u003c\/p\u003e \u003cp\u003eOther Counting Methods 165\u003c\/p\u003e \u003cp\u003eBasic Counting Formulas 165\u003c\/p\u003e \u003cp\u003eCounting the total number of cells 166\u003c\/p\u003e \u003cp\u003eCounting blank cells 166\u003c\/p\u003e \u003cp\u003eCounting nonblank cells 167\u003c\/p\u003e \u003cp\u003eCounting numeric cells 167\u003c\/p\u003e \u003cp\u003eCounting text cells 168\u003c\/p\u003e \u003cp\u003eCounting nontext cells 168\u003c\/p\u003e \u003cp\u003eCounting logical values 168\u003c\/p\u003e \u003cp\u003eCounting error values in a range 168\u003c\/p\u003e \u003cp\u003eAdvanced Counting Formulas 169\u003c\/p\u003e \u003cp\u003eCounting cells with the COUNTIF function 169\u003c\/p\u003e \u003cp\u003eCounting cells that meet multiple criteria 170\u003c\/p\u003e \u003cp\u003eCounting the most frequently occurring entry 173\u003c\/p\u003e \u003cp\u003eCounting the occurrences of specific text 174\u003c\/p\u003e \u003cp\u003eCounting the number of unique values 176\u003c\/p\u003e \u003cp\u003eCreating a frequency distribution 178\u003c\/p\u003e \u003cp\u003eSumming Formulas 184\u003c\/p\u003e \u003cp\u003eSumming all cells in a range 184\u003c\/p\u003e \u003cp\u003eSumming a range that contains errors 185\u003c\/p\u003e \u003cp\u003eComputing a cumulative sum 186\u003c\/p\u003e \u003cp\u003eSumming the “top n” values 187\u003c\/p\u003e \u003cp\u003eConditional Sums Using a Single Criterion 188\u003c\/p\u003e \u003cp\u003eSumming only negative values 189\u003c\/p\u003e \u003cp\u003eSumming values based on a different range 190\u003c\/p\u003e \u003cp\u003eSumming values based on a text comparison 190\u003c\/p\u003e \u003cp\u003eSumming values based on a date comparison 190\u003c\/p\u003e \u003cp\u003eConditional Sums Using Multiple Criteria 191\u003c\/p\u003e \u003cp\u003eUsing And criteria 191\u003c\/p\u003e \u003cp\u003eUsing Or criteria 192\u003c\/p\u003e \u003cp\u003eUsing And and Or criteria 193\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 8: Using Lookup Functions 195\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eWhat Is a Lookup Formula? 195\u003c\/p\u003e \u003cp\u003eFunctions Relevant to Lookups 196\u003c\/p\u003e \u003cp\u003eBasic Lookup Formulas 198\u003c\/p\u003e \u003cp\u003eThe VLOOKUP function 198\u003c\/p\u003e \u003cp\u003eThe HLOOKUP function 200\u003c\/p\u003e \u003cp\u003eThe LOOKUP function 201\u003c\/p\u003e \u003cp\u003eCombining the MATCH and INDEX functions 202\u003c\/p\u003e \u003cp\u003eSpecialized Lookup Formulas 203\u003c\/p\u003e \u003cp\u003eLooking up an exact value 204\u003c\/p\u003e \u003cp\u003eLooking up a value to the left 206\u003c\/p\u003e \u003cp\u003ePerforming a case-sensitive lookup 207\u003c\/p\u003e \u003cp\u003eChoosing among multiple lookup tables 207\u003c\/p\u003e \u003cp\u003eDetermining letter grades for test scores 208\u003c\/p\u003e \u003cp\u003eCalculating a grade point average 209\u003c\/p\u003e \u003cp\u003ePerforming a two-way lookup 211\u003c\/p\u003e \u003cp\u003ePerforming a two-column lookup 212\u003c\/p\u003e \u003cp\u003eDetermining the address of a value within a range 213\u003c\/p\u003e \u003cp\u003eLooking up a value by using the closest match 214\u003c\/p\u003e \u003cp\u003eLooking up a value using linear interpolation 215\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 9: Working with Tables and Lists 219\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eTables and Terminology 219\u003c\/p\u003e \u003cp\u003eA list example 220\u003c\/p\u003e \u003cp\u003eA table example 220\u003c\/p\u003e \u003cp\u003eWorking with Tables 222\u003c\/p\u003e \u003cp\u003eCreating a table 222\u003c\/p\u003e \u003cp\u003eChanging the look of a table 223\u003c\/p\u003e \u003cp\u003eNavigating and selecting in a table 224\u003c\/p\u003e \u003cp\u003eAdding new rows or columns 225\u003c\/p\u003e \u003cp\u003eDeleting rows or columns 226\u003c\/p\u003e \u003cp\u003eMoving a table 226\u003c\/p\u003e \u003cp\u003eRemoving duplicate rows from a table 227\u003c\/p\u003e \u003cp\u003eSorting and filtering a table 228\u003c\/p\u003e \u003cp\u003eWorking with the Total row 233\u003c\/p\u003e \u003cp\u003eUsing formulas within a table 235\u003c\/p\u003e \u003cp\u003eReferencing data in a table 237\u003c\/p\u003e \u003cp\u003eConverting a table to a list 241\u003c\/p\u003e \u003cp\u003eUsing Advanced Filtering 242\u003c\/p\u003e \u003cp\u003eSetting up a criteria range 242\u003c\/p\u003e \u003cp\u003eApplying an advanced filter 243\u003c\/p\u003e \u003cp\u003eClearing an advanced filter 245\u003c\/p\u003e \u003cp\u003eSpecifying Advanced Filter Criteria 245\u003c\/p\u003e \u003cp\u003eSpecifying a single criterion 245\u003c\/p\u003e \u003cp\u003eSpecifying multiple criteria 247\u003c\/p\u003e \u003cp\u003eSpecifying computed criteria 249\u003c\/p\u003e \u003cp\u003eUsing Database Functions 250\u003c\/p\u003e \u003cp\u003eInserting Subtotals 252\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 10: Miscellaneous Calculations 257\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eUnit Conversions 257\u003c\/p\u003e \u003cp\u003eRounding Numbers 261\u003c\/p\u003e \u003cp\u003eBasic rounding formulas 262\u003c\/p\u003e \u003cp\u003eRounding to the nearest multiple 263\u003c\/p\u003e \u003cp\u003eRounding currency values 263\u003c\/p\u003e \u003cp\u003eWorking with fractional dollars 264\u003c\/p\u003e \u003cp\u003eUsing the INT and TRUNC functions 265\u003c\/p\u003e \u003cp\u003eRounding to an even or odd integer 266\u003c\/p\u003e \u003cp\u003eRounding to n significant digits 267\u003c\/p\u003e \u003cp\u003eSolving Right Triangles 267\u003c\/p\u003e \u003cp\u003eArea, Surface, Circumference, and Volume Calculations 270\u003c\/p\u003e \u003cp\u003eCalculating the area and perimeter of a square 270\u003c\/p\u003e \u003cp\u003eCalculating the area and perimeter of a rectangle 270\u003c\/p\u003e \u003cp\u003eCalculating the area and perimeter of a circle 270\u003c\/p\u003e \u003cp\u003eCalculating the area of a trapezoid 271\u003c\/p\u003e \u003cp\u003eCalculating the area of a triangle 271\u003c\/p\u003e \u003cp\u003eCalculating the surface and volume of a sphere 271\u003c\/p\u003e \u003cp\u003eCalculating the surface and volume of a cube 271\u003c\/p\u003e \u003cp\u003eCalculating the surface and volume of a rectangular solid 272\u003c\/p\u003e \u003cp\u003eCalculating the surface and volume of a cone 272\u003c\/p\u003e \u003cp\u003eCalculating the volume of a cylinder 272\u003c\/p\u003e \u003cp\u003eCalculating the volume of a pyramid 273\u003c\/p\u003e \u003cp\u003eSolving Simultaneous Equations 273\u003c\/p\u003e \u003cp\u003eWorking with Normal Distributions 274\u003c\/p\u003e \u003cp\u003e\u003cb\u003ePart III: Financial Formulas\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 11: Borrowing and Investing Formulas 279\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eThe Time Value of Money 279\u003c\/p\u003e \u003cp\u003eLoan Calculations 280\u003c\/p\u003e \u003cp\u003eWorksheet functions for calculating loan information 281\u003c\/p\u003e \u003cp\u003eA loan calculation example 284\u003c\/p\u003e \u003cp\u003eCredit card payments 285\u003c\/p\u003e \u003cp\u003eCreating a loan amortization schedule 287\u003c\/p\u003e \u003cp\u003eCalculating a loan with irregular payments 288\u003c\/p\u003e \u003cp\u003eInvestment Calculations 290\u003c\/p\u003e \u003cp\u003eFuture value of a single deposit 290\u003c\/p\u003e \u003cp\u003ePresent value of a series of payments 296\u003c\/p\u003e \u003cp\u003eFuture value of a series of deposits 296\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 12: Discounting and Depreciation Formulas 299\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eUsing the NPV Function 299\u003c\/p\u003e \u003cp\u003eDefinition of NPV 300\u003c\/p\u003e \u003cp\u003eNPV function examples 301\u003c\/p\u003e \u003cp\u003eUsing the IRR Function 306\u003c\/p\u003e \u003cp\u003eRate of return 307\u003c\/p\u003e \u003cp\u003eGeometric growth rates 308\u003c\/p\u003e \u003cp\u003eChecking results 309\u003c\/p\u003e \u003cp\u003eIrregular Cash Flows 310\u003c\/p\u003e \u003cp\u003eNet present value 310\u003c\/p\u003e \u003cp\u003eInternal rate of return 311\u003c\/p\u003e \u003cp\u003eDepreciation Calculations 312\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 13: Financial Schedules 317\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eCreating Financial Schedules 317\u003c\/p\u003e \u003cp\u003eCreating Amortization Schedules 318\u003c\/p\u003e \u003cp\u003eA simple amortization schedule 318\u003c\/p\u003e \u003cp\u003eA dynamic amortization schedule 320\u003c\/p\u003e \u003cp\u003eCredit card calculations 323\u003c\/p\u003e \u003cp\u003eSummarizing Loan Options Using a Data Table 325\u003c\/p\u003e \u003cp\u003eCreating a one-way data table 325\u003c\/p\u003e \u003cp\u003eCreating a two-way data table 327\u003c\/p\u003e \u003cp\u003eFinancial Statements and Ratios 329\u003c\/p\u003e \u003cp\u003eBasic financial statements 329\u003c\/p\u003e \u003cp\u003eRatio analysis 333\u003c\/p\u003e \u003cp\u003eCreating Indices 337\u003c\/p\u003e \u003cp\u003e\u003cb\u003ePart IV: Array Formulas\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 14: Introducing Arrays 341\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eIntroducing Array Formulas 341\u003c\/p\u003e \u003cp\u003eA multicell array formula 342\u003c\/p\u003e \u003cp\u003eA single‐cell array formula 343\u003c\/p\u003e \u003cp\u003eCreating an array constant 344\u003c\/p\u003e \u003cp\u003eArray constant elements 345\u003c\/p\u003e \u003cp\u003eUnderstanding the Dimensions of an Array 346\u003c\/p\u003e \u003cp\u003eOne‐dimensional horizontal arrays 346\u003c\/p\u003e \u003cp\u003eOne‐dimensional vertical arrays 347\u003c\/p\u003e \u003cp\u003eTwo‐dimensional arrays 347\u003c\/p\u003e \u003cp\u003eNaming Array Constants 349\u003c\/p\u003e \u003cp\u003eWorking with Array Formulas 350\u003c\/p\u003e \u003cp\u003eEntering an array formula 350\u003c\/p\u003e \u003cp\u003eSelecting an array formula range 350\u003c\/p\u003e \u003cp\u003eEditing an array formula 351\u003c\/p\u003e \u003cp\u003eExpanding or contracting a multicell array formula 352\u003c\/p\u003e \u003cp\u003eUsing Multicell Array Formulas 353\u003c\/p\u003e \u003cp\u003eCreating an array from values in a range 353\u003c\/p\u003e \u003cp\u003eCreating an array constant from values in a range 353\u003c\/p\u003e \u003cp\u003ePerforming operations on an array 354\u003c\/p\u003e \u003cp\u003eUsing functions with an array 355\u003c\/p\u003e \u003cp\u003eTransposing an array 355\u003c\/p\u003e \u003cp\u003eGenerating an array of consecutive integers 357\u003c\/p\u003e \u003cp\u003eUsing Single‐Cell Array Formulas 358\u003c\/p\u003e \u003cp\u003eCounting characters in a range 358\u003c\/p\u003e \u003cp\u003eSumming the three smallest values in a range 359\u003c\/p\u003e \u003cp\u003eCounting text cells in a range 360\u003c\/p\u003e \u003cp\u003eEliminating intermediate formulas 362\u003c\/p\u003e \u003cp\u003eUsing an array in lieu of a range reference 364\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 15: Performing Magic with Array Formulas 365\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eWorking with Single‐Cell Array Formulas 365\u003c\/p\u003e \u003cp\u003eSumming a range that contains errors 366\u003c\/p\u003e \u003cp\u003eCounting the number of error values in a range 367\u003c\/p\u003e \u003cp\u003eSumming the n largest values in a range 368\u003c\/p\u003e \u003cp\u003eComputing an average that excludes zeros 368\u003c\/p\u003e \u003cp\u003eDetermining whether a particular value appears in a range 369\u003c\/p\u003e \u003cp\u003eCounting the number of differences in two ranges 371\u003c\/p\u003e \u003cp\u003eReturning the location of the maximum value in a range 372\u003c\/p\u003e \u003cp\u003eFinding the row of a value’s nth occurrence in a range 373\u003c\/p\u003e \u003cp\u003eReturning the longest text in a range 373\u003c\/p\u003e \u003cp\u003eDetermining whether a range contains valid values 374\u003c\/p\u003e \u003cp\u003eSumming the digits of an integer 375\u003c\/p\u003e \u003cp\u003eSumming rounded values 377\u003c\/p\u003e \u003cp\u003eSumming every nth value in a range 377\u003c\/p\u003e \u003cp\u003eRemoving nonnumeric characters from a string 379\u003c\/p\u003e \u003cp\u003eDetermining the closest value in a range 380\u003c\/p\u003e \u003cp\u003eReturning the last value in a column 380\u003c\/p\u003e \u003cp\u003eReturning the last value in a row 381\u003c\/p\u003e \u003cp\u003eWorking with Multicell Array Formulas 382\u003c\/p\u003e \u003cp\u003eReturning only positive values from a range 382\u003c\/p\u003e \u003cp\u003eReturning nonblank cells from a range 384\u003c\/p\u003e \u003cp\u003eReversing the order of cells in a range 384\u003c\/p\u003e \u003cp\u003eSorting a range of values dynamically 385\u003c\/p\u003e \u003cp\u003eReturning a list of unique items in a range 386\u003c\/p\u003e \u003cp\u003eDisplaying a calendar in a range 387\u003c\/p\u003e \u003cp\u003e\u003cb\u003ePart V: Miscellaneous Formula Techniques\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 16: Importing and Cleaning Data 393\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eA Few Words About Data 393\u003c\/p\u003e \u003cp\u003eImporting Data 394\u003c\/p\u003e \u003cp\u003eImporting from a file 394\u003c\/p\u003e \u003cp\u003eImporting a text file into a specified range 396\u003c\/p\u003e \u003cp\u003eCopying and pasting data 398\u003c\/p\u003e \u003cp\u003eData Cleanup Techniques 398\u003c\/p\u003e \u003cp\u003eRemoving duplicate rows 398\u003c\/p\u003e \u003cp\u003eIdentifying duplicate rows 400\u003c\/p\u003e \u003cp\u003eSplitting text 401\u003c\/p\u003e \u003cp\u003eChanging the case of text 407\u003c\/p\u003e \u003cp\u003eRemoving extra spaces 408\u003c\/p\u003e \u003cp\u003eRemoving strange characters 409\u003c\/p\u003e \u003cp\u003eConverting values 409\u003c\/p\u003e \u003cp\u003eClassifying values 410\u003c\/p\u003e \u003cp\u003eJoining columns 411\u003c\/p\u003e \u003cp\u003eRearranging columns 412\u003c\/p\u003e \u003cp\u003eRandomizing the rows 412\u003c\/p\u003e \u003cp\u003eMatching text in a list 413\u003c\/p\u003e \u003cp\u003eChange vertical data to horizontal data 414\u003c\/p\u003e \u003cp\u003eFilling gaps in an imported report 417\u003c\/p\u003e \u003cp\u003eSpelling checking 418\u003c\/p\u003e \u003cp\u003eReplacing or removing text in cells 419\u003c\/p\u003e \u003cp\u003eAdding text to cells 420\u003c\/p\u003e \u003cp\u003eFixing trailing minus signs 420\u003c\/p\u003e \u003cp\u003eA Data Cleaning Checklist 421\u003c\/p\u003e \u003cp\u003eExporting Data 422\u003c\/p\u003e \u003cp\u003eExporting to a text file 422\u003c\/p\u003e \u003cp\u003eExporting to other file formats 423\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 17: Charting Techniques 425\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eUnderstanding the SERIES Formula 425\u003c\/p\u003e \u003cp\u003eUsing names in a SERIES formula 427\u003c\/p\u003e \u003cp\u003eUnlinking a chart series from its data range 428\u003c\/p\u003e \u003cp\u003eCreating Links to Cells 429\u003c\/p\u003e \u003cp\u003eAdding a chart title link 429\u003c\/p\u003e \u003cp\u003eAdding axis title links 430\u003c\/p\u003e \u003cp\u003eAdding text links 430\u003c\/p\u003e \u003cp\u003eAdding a linked picture to a chart 430\u003c\/p\u003e \u003cp\u003eChart Examples 431\u003c\/p\u003e \u003cp\u003eSingle data point charts 431\u003c\/p\u003e \u003cp\u003eDisplaying conditional colors in a column chart 433\u003c\/p\u003e \u003cp\u003eCreating a comparative histogram 434\u003c\/p\u003e \u003cp\u003eCreating a Gantt chart 435\u003c\/p\u003e \u003cp\u003eCreating a box plot 438\u003c\/p\u003e \u003cp\u003ePlotting every nth data point 439\u003c\/p\u003e \u003cp\u003eIdentifying maximum and minimum values in a chart 441\u003c\/p\u003e \u003cp\u003eCreating a Timeline 442\u003c\/p\u003e \u003cp\u003ePlotting mathematical functions 443\u003c\/p\u003e \u003cp\u003ePlotting a circle 448\u003c\/p\u003e \u003cp\u003eCreating a clock chart 450\u003c\/p\u003e \u003cp\u003eCreating awesome designs 452\u003c\/p\u003e \u003cp\u003eWorking with Trendlines 453\u003c\/p\u003e \u003cp\u003eLinear trendlines 454\u003c\/p\u003e \u003cp\u003eWorking with nonlinear trendlines 460\u003c\/p\u003e \u003cp\u003eSummary of trendline equations 461\u003c\/p\u003e \u003cp\u003eCreating Interactive Charts 462\u003c\/p\u003e \u003cp\u003eSelecting a series from a drop‐down list 462\u003c\/p\u003e \u003cp\u003ePlotting the last n data points 463\u003c\/p\u003e \u003cp\u003eChoosing a start date and number of points 464\u003c\/p\u003e \u003cp\u003eDisplaying population data 465\u003c\/p\u003e \u003cp\u003eDisplaying weather data 465\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 18: Pivot Tables 469\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eAbout Pivot Tables 469\u003c\/p\u003e \u003cp\u003eA Pivot Table Example 470\u003c\/p\u003e \u003cp\u003eData Appropriate for a Pivot Table 472\u003c\/p\u003e \u003cp\u003eCreating a Pivot Table Automatically 475\u003c\/p\u003e \u003cp\u003eCreating a Pivot Table Manually 477\u003c\/p\u003e \u003cp\u003eSpecifying the data 477\u003c\/p\u003e \u003cp\u003eSpecifying the location for the pivot table 478\u003c\/p\u003e \u003cp\u003eLaying out the pivot table 480\u003c\/p\u003e \u003cp\u003eFormatting the pivot table 481\u003c\/p\u003e \u003cp\u003eModifying the pivot table 483\u003c\/p\u003e \u003cp\u003eMore Pivot Table Examples 485\u003c\/p\u003e \u003cp\u003eQuestion 1 485\u003c\/p\u003e \u003cp\u003eQuestion 2 486\u003c\/p\u003e \u003cp\u003eQuestion 3 487\u003c\/p\u003e \u003cp\u003eQuestion 4 487\u003c\/p\u003e \u003cp\u003eQuestion 5 488\u003c\/p\u003e \u003cp\u003eQuestion 6 489\u003c\/p\u003e \u003cp\u003eQuestion 7 490\u003c\/p\u003e \u003cp\u003eGrouping Pivot Table Items 491\u003c\/p\u003e \u003cp\u003eA manual grouping example 491\u003c\/p\u003e \u003cp\u003eViewing grouped data 493\u003c\/p\u003e \u003cp\u003eAutomatic grouping examples 494\u003c\/p\u003e \u003cp\u003eCreating a Frequency Distribution 498\u003c\/p\u003e \u003cp\u003eCreating a Calculated Field or Calculated Item 499\u003c\/p\u003e \u003cp\u003eCreating a calculated field 501\u003c\/p\u003e \u003cp\u003eInserting a calculated item 503\u003c\/p\u003e \u003cp\u003eFiltering Pivot Tables with Slicers 506\u003c\/p\u003e \u003cp\u003eFiltering Pivot Tables with a Timeline 507\u003c\/p\u003e \u003cp\u003eReferencing Cells Within a Pivot Table 508\u003c\/p\u003e \u003cp\u003eAnother Pivot Table Example 510\u003c\/p\u003e \u003cp\u003eUsing the Data Model 513\u003c\/p\u003e \u003cp\u003eCreating Pivot Charts 516\u003c\/p\u003e \u003cp\u003eA pivot chart example 517\u003c\/p\u003e \u003cp\u003eMore about pivot charts 519\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 19: Conditional Formatting 521\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eAbout Conditional Formatting 521\u003c\/p\u003e \u003cp\u003eSpecifying Conditional Formatting 523\u003c\/p\u003e \u003cp\u003eFormatting types you can apply 523\u003c\/p\u003e \u003cp\u003eMaking your own rules 524\u003c\/p\u003e \u003cp\u003eConditional Formats That Use Graphics 525\u003c\/p\u003e \u003cp\u003eUsing data bars 525\u003c\/p\u003e \u003cp\u003eUsing color scales 527\u003c\/p\u003e \u003cp\u003eUsing icon sets 530\u003c\/p\u003e \u003cp\u003eCreating Formula-Based Rules 533\u003c\/p\u003e \u003cp\u003eUnderstanding relative and absolute references 534\u003c\/p\u003e \u003cp\u003eConditional formatting formula examples 536\u003c\/p\u003e \u003cp\u003eWorking with Conditional Formats 543\u003c\/p\u003e \u003cp\u003eManaging rules 544\u003c\/p\u003e \u003cp\u003eCopying cells that contain conditional formatting 544\u003c\/p\u003e \u003cp\u003eDeleting conditional formatting 545\u003c\/p\u003e \u003cp\u003eLocating cells that contain conditional formatting 545\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 20: Using Data Validation 547\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eAbout Data Validation 547\u003c\/p\u003e \u003cp\u003eSpecifying Validation Criteria 548\u003c\/p\u003e \u003cp\u003eTypes of Validation Criteria You Can Apply 549\u003c\/p\u003e \u003cp\u003eCreating a Drop‐Down List 551\u003c\/p\u003e \u003cp\u003eUsing Formulas for Data Validation Rules 552\u003c\/p\u003e \u003cp\u003eUnderstanding Cell References 552\u003c\/p\u003e \u003cp\u003eData Validation Formula Examples 554\u003c\/p\u003e \u003cp\u003eAccepting text only 554\u003c\/p\u003e \u003cp\u003eAccepting a larger value than the previous cell 554\u003c\/p\u003e \u003cp\u003eAccepting nonduplicate entries only 554\u003c\/p\u003e \u003cp\u003eAccepting text that begins with a specific character 555\u003c\/p\u003e \u003cp\u003eAccepting dates by the day of the week 556\u003c\/p\u003e \u003cp\u003eAccepting only values that don’t exceed a total 556\u003c\/p\u003e \u003cp\u003eCreating a dependent list 557\u003c\/p\u003e \u003cp\u003eUsing Structured Table Referencing 558\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 21: Creating Megaformulas 561\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eWhat Is a Megaformula? 561\u003c\/p\u003e \u003cp\u003eCreating a Megaformula: A Simple Example 562\u003c\/p\u003e \u003cp\u003eMegaformula Examples 564\u003c\/p\u003e \u003cp\u003eUsing a megaformula to remove middle names 564\u003c\/p\u003e \u003cp\u003eUsing a megaformula to return a string’s last space character position 569\u003c\/p\u003e \u003cp\u003eUsing a megaformula to determine the validity of a credit card number 573\u003c\/p\u003e \u003cp\u003eUsing Intermediate Named Formulas 578\u003c\/p\u003e \u003cp\u003eGenerating random names 579\u003c\/p\u003e \u003cp\u003eThe Pros and Cons of Megaformulas 580\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 22: Tools and Methods for Debugging Formulas 581\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eFormula Debugging? 581\u003c\/p\u003e \u003cp\u003eFormula Problems and Solutions 582\u003c\/p\u003e \u003cp\u003eMismatched parentheses 583\u003c\/p\u003e \u003cp\u003eCells are filled with hash marks 584\u003c\/p\u003e \u003cp\u003eBlank cells are not blank 584\u003c\/p\u003e \u003cp\u003eExtra space characters 585\u003c\/p\u003e \u003cp\u003eFormulas returning an error 585\u003c\/p\u003e \u003cp\u003eAbsolute\/relative reference problems 590\u003c\/p\u003e \u003cp\u003eOperator precedence problems 591\u003c\/p\u003e \u003cp\u003eFormulas are not calculated 592\u003c\/p\u003e \u003cp\u003eActual versus displayed values 592\u003c\/p\u003e \u003cp\u003eFloating‐point number errors 593\u003c\/p\u003e \u003cp\u003ePhantom link errors 594\u003c\/p\u003e \u003cp\u003eLogical value errors 595\u003c\/p\u003e \u003cp\u003eCircular reference errors 596\u003c\/p\u003e \u003cp\u003eExcel’s Auditing Tools 596\u003c\/p\u003e \u003cp\u003eIdentifying cells of a particular type 596\u003c\/p\u003e \u003cp\u003eViewing formulas 597\u003c\/p\u003e \u003cp\u003eTracing cell relationships 598\u003c\/p\u003e \u003cp\u003eTracing error values 600\u003c\/p\u003e \u003cp\u003eFixing circular reference errors 600\u003c\/p\u003e \u003cp\u003eUsing background error checking 600\u003c\/p\u003e \u003cp\u003eUsing Excel’s Formula Evaluator 603\u003c\/p\u003e \u003cp\u003e\u003cb\u003ePart VI: Developing Custom Worksheet Functions\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 23: Introducing VBA 607\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eFundamental Macro Concepts 607\u003c\/p\u003e \u003cp\u003eActivating the Developer tab 608\u003c\/p\u003e \u003cp\u003eRecording a macro 608\u003c\/p\u003e \u003cp\u003eUnderstanding macro-enabled extensions 611\u003c\/p\u003e \u003cp\u003eMacro security in Excel 611\u003c\/p\u003e \u003cp\u003eTrusted locations 611\u003c\/p\u003e \u003cp\u003eStoring macros in your Personal Macro Workbook 612\u003c\/p\u003e \u003cp\u003eAssigning a macro to a button and other form controls 612\u003c\/p\u003e \u003cp\u003ePlacing a macro on the Quick Access toolbar 614\u003c\/p\u003e \u003cp\u003eWorking in the Visual Basic Editor 615\u003c\/p\u003e \u003cp\u003eUnderstanding VBE components 615\u003c\/p\u003e \u003cp\u003eWorking with the Project window 616\u003c\/p\u003e \u003cp\u003eWorking with a code window 619\u003c\/p\u003e \u003cp\u003eCustomizing the VBA environment 622\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 24: VBA Programming Concepts 627\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eA Brief Overview of the Excel Object Model 627\u003c\/p\u003e \u003cp\u003eUnderstanding objects 628\u003c\/p\u003e \u003cp\u003eUnderstanding collections 628\u003c\/p\u003e \u003cp\u003eUnderstanding properties 629\u003c\/p\u003e \u003cp\u003eUnderstanding methods 629\u003c\/p\u003e \u003cp\u003eA brief look at variables 630\u003c\/p\u003e \u003cp\u003eError handling 633\u003c\/p\u003e \u003cp\u003eUsing code comments 636\u003c\/p\u003e \u003cp\u003eAn Introductory Example Function Procedure 636\u003c\/p\u003e \u003cp\u003eUsing Built-In VBA Functions 638\u003c\/p\u003e \u003cp\u003eControlling Execution 640\u003c\/p\u003e \u003cp\u003eThe If-Then construct 640\u003c\/p\u003e \u003cp\u003eThe Select Case construct 642\u003c\/p\u003e \u003cp\u003eLooping blocks of instructions 643\u003c\/p\u003e \u003cp\u003eUsing Ranges 648\u003c\/p\u003e \u003cp\u003eThe For Each-Next construct 648\u003c\/p\u003e \u003cp\u003eReferencing a range 649\u003c\/p\u003e \u003cp\u003eSome useful properties of ranges 651\u003c\/p\u003e \u003cp\u003eThe Set keyword 655\u003c\/p\u003e \u003cp\u003eThe Intersect function 655\u003c\/p\u003e \u003cp\u003eThe Union function 656\u003c\/p\u003e \u003cp\u003eThe UsedRange property 656\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 25: Function Procedure Basics 659\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eWhy Create Custom Functions? 659\u003c\/p\u003e \u003cp\u003eAn Introductory VBA Function Example 660\u003c\/p\u003e \u003cp\u003eAbout Function Procedures 662\u003c\/p\u003e \u003cp\u003eDeclaring a function 662\u003c\/p\u003e \u003cp\u003eChoosing a name for your function 663\u003c\/p\u003e \u003cp\u003eUsing functions in formulas 664\u003c\/p\u003e \u003cp\u003eUsing function arguments 665\u003c\/p\u003e \u003cp\u003eUsing the Insert Function Dialog Box 665\u003c\/p\u003e \u003cp\u003eAdding a function description 666\u003c\/p\u003e \u003cp\u003eSpecifying a function category 667\u003c\/p\u003e \u003cp\u003eAdding argument descriptions 669\u003c\/p\u003e \u003cp\u003eTesting and Debugging Your Functions 670\u003c\/p\u003e \u003cp\u003eUsing the VBA MsgBox statement 671\u003c\/p\u003e \u003cp\u003eUsing Debug.Print statements in your code 673\u003c\/p\u003e \u003cp\u003eCalling the function from a Sub procedure 673\u003c\/p\u003e \u003cp\u003eSetting a breakpoint in the function 676\u003c\/p\u003e \u003cp\u003eCreating Add-Ins for Functions 676\u003c\/p\u003e \u003cp\u003e\u003cb\u003eChapter 26: VBA Custom Function Examples 679\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eSimple Functions 679\u003c\/p\u003e \u003cp\u003eIs the cell hidden? 680\u003c\/p\u003e \u003cp\u003eReturning a worksheet name 680\u003c\/p\u003e \u003cp\u003eReturning a workbook name 681\u003c\/p\u003e \u003cp\u003eReturning the application’s name 681\u003c\/p\u003e \u003cp\u003eReturning Excel’s version number 682\u003c\/p\u003e \u003cp\u003eReturning cell formatting information 682\u003c\/p\u003e \u003cp\u003eDetermining a Cell’s Data Type 684\u003c\/p\u003e \u003cp\u003eA Multifunctional Function 685\u003c\/p\u003e \u003cp\u003eGenerating Random Numbers 688\u003c\/p\u003e \u003cp\u003eGenerating random numbers that don’t change 688\u003c\/p\u003e \u003cp\u003eSelecting a cell at random 690\u003c\/p\u003e \u003cp\u003eCalculating Sales Commissions 691\u003c\/p\u003e \u003cp\u003eA function for a simple commission structure 691\u003c\/p\u003e \u003cp\u003eA function for a more complex commission structure 692\u003c\/p\u003e \u003cp\u003eText Manipulation Functions 693\u003c\/p\u003e \u003cp\u003eReversing a string 694\u003c\/p\u003e \u003cp\u003eScrambling text 694\u003c\/p\u003e \u003cp\u003eReturning an acronym 695\u003c\/p\u003e \u003cp\u003eDoes the text match a pattern? 695\u003c\/p\u003e \u003cp\u003eDoes a cell contain a particular word? 696\u003c\/p\u003e \u003cp\u003eDoes a cell contain text? 698\u003c\/p\u003e \u003cp\u003eExtracting the nth element from a string 698\u003c\/p\u003e \u003cp\u003eSpelling out a number 699\u003c\/p\u003e \u003cp\u003eCounting Functions 700\u003c\/p\u003e \u003cp\u003eCounting pattern-matched cells 700\u003c\/p\u003e \u003cp\u003eCounting sheets in a workbook 700\u003c\/p\u003e \u003cp\u003eCounting words in a range 701\u003c\/p\u003e \u003cp\u003eDate Functions 701\u003c\/p\u003e \u003cp\u003eCalculating the next Monday 702\u003c\/p\u003e \u003cp\u003eCalculating the next day of the week 702\u003c\/p\u003e \u003cp\u003eWhich week of the month? 703\u003c\/p\u003e \u003cp\u003eWorking with dates before 1900 703\u003c\/p\u003e \u003cp\u003eReturning the Last Nonempty Cell in a Column or Row 704\u003c\/p\u003e \u003cp\u003eThe LASTINCOLUMN function 705\u003c\/p\u003e \u003cp\u003eThe LASTINROW function 705\u003c\/p\u003e \u003cp\u003eMultisheet Functions 706\u003c\/p\u003e \u003cp\u003eReturning the maximum value across all worksheets 706\u003c\/p\u003e \u003cp\u003eThe SHEETOFFSET function 708\u003c\/p\u003e \u003cp\u003eAdvanced Function Techniques 709\u003c\/p\u003e \u003cp\u003eReturning an error value 709\u003c\/p\u003e \u003cp\u003eReturning an array from a function 710\u003c\/p\u003e \u003cp\u003eReturning an array of nonduplicated random integers 712\u003c\/p\u003e \u003cp\u003eRandomizing a range 714\u003c\/p\u003e \u003cp\u003eUsing optional arguments 716\u003c\/p\u003e \u003cp\u003eUsing an indefinite number of arguments 717\u003c\/p\u003e \u003cp\u003e\u003cb\u003ePart VII: Appendixes\u003c\/b\u003e\u003c\/p\u003e \u003cp\u003eAppendix A: Excel Function Reference 725\u003c\/p\u003e \u003cp\u003eAppendix B: Using Custom Number Formats 743\u003c\/p\u003e \u003cp\u003eIndex 763\u003c\/p\u003e   \u003cp\u003e\u003cb\u003eMichael Alexander\u003c\/b\u003e 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.  \u003c\/p\u003e\u003cp\u003e\u003cb\u003eDick Kusleika\u003c\/b\u003e develops Access- and Excel-based solutions, and conducts Office training seminars in the U.S. and Australia. He writes the popular blog dailydoseofexcel.com. \u003c\/p\u003e\u003cp\u003e\u003cb\u003eJohn Walkenbach\u003c\/b\u003e 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.     \u003c\/p\u003e\u003cp\u003e\u003cb\u003e\u003ci\u003eUnleash the Power of Excel with Expert Techniques\u003c\/i\u003e\u003c\/b\u003e  \u003c\/p\u003e\u003cp\u003eIf you're only using Excel to create simple tables and charts, it's time to dig into the software's true functionality: formulas. This book is your ideal guide, dedicated to comprehensive formula coverage that will show you just how much Excel can do for you.  \u003c\/p\u003e\u003cp\u003eYou'll learn the tips and tricks that make your workflow faster, easier, and more accurate. You'll learn the expert techniques that you won't find anywhere else. Whether you're a beginner or a power-user, this essential reference will take you deep into Excel to maximize the power of the 2016 upgrade.  \u003c\/p\u003e\u003cp\u003e\u003cb\u003e\u003ci\u003eLet Mr. Spreadsheet show you how to:\u003c\/i\u003e\u003c\/b\u003e \u003c\/p\u003e\u003cul\u003e \u003cli\u003eLeverage formulas to optimize productivity\u003c\/li\u003e \u003cli\u003ePerform Financial and Statistical analyses\u003c\/li\u003e \u003cli\u003eWork with pivot tables and conditional formatting\u003c\/li\u003e \u003cli\u003eCreate your own custom functions using VBA\u003c\/li\u003e \u003cli\u003eDebug formulas and avoid common errors\u003c\/li\u003e \u003c\/ul\u003e \u003cp\u003e\u003cb\u003eCOMPANION WEBSITE\u003c\/b\u003e  \u003c\/p\u003e\u003cp\u003eVisit www.wiley.com\/go\/excel2016formulas to download workbook files for all examples used in the book.\u003c\/p\u003e","brand":"Wiley","offers":[{"title":"Default Title","offer_id":47989182071013,"sku":"NP9781119067863","price":57.0,"currency_code":"USD","in_stock":false}],"thumbnail_url":"\/\/cdn.shopify.com\/s\/files\/1\/1842\/7735\/files\/9781119067863.jpg?v=1761783117","url":"https:\/\/k12savings.com\/es\/products\/excel-2016-formulas-isbn-9781119067863","provider":"K12savings","version":"1.0","type":"link"}